aspose file tools*
The moose likes JDBC and the fly likes how to connect MS Access directly Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to connect MS Access directly" Watch "how to connect MS Access directly" New topic
Author

how to connect MS Access directly

Will Lee
Ranch Hand

Joined: Mar 16, 2005
Posts: 58
I mean connect to Access tables withouth register .MDb as ODBC data source? I need to deal with many .mdb file without knowing their name (but only know it's *.mdb), and it's impossible to let user register each mdb file. Is there a way to read data from file, or connect to DB directly?
Thanks.


SCJP 1.4, SCWCD, SCDJWS
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
As far as I know there is no way around other then ODBC DSN.
Sirish Kumar Gongal Reddy
Ranch Hand

Joined: Oct 25, 2004
Posts: 109
Hi Will Lee ,
It's very simple i will tell you,
ResuletSet rs=stmt.executeQuery("select * from [Emp$];");
while(rs.next()){
}
I hope that your are comfottable with rest of code.
Emp: is a table name.

Regards,
G Sirish Reddy.,
David Ulicny
Ranch Hand

Joined: Aug 04, 2004
Posts: 724
That is exactly what Will don't want to do


SCJP<br />SCWCD <br />ICSD(286)<br />MCP 70-216
Sirish Kumar Gongal Reddy
Ranch Hand

Joined: Oct 25, 2004
Posts: 109
Hi David Ulicny ,
what about your comment for my above code plz let me know.
Regards,
G Sirish Reddy.,
David Ulicny
Ranch Hand

Joined: Aug 04, 2004
Posts: 724
Your code is fine, but as I understand Will question, he wants read data from mdb file directly, not through JDBC or ODBC, as you suggested.
He wants to do something like:

openTheMdbFile();
readDataFormMdbFile();
doSomethingWithData();
Will Lee
Ranch Hand

Joined: Mar 16, 2005
Posts: 58
Thank you guys. Yes, I'm working on an open microscopy environment project (OME) that need to read and parse data from many formats into XML file. The last format I left is the .mdb file. Since there could be uncertain amount of .mdb files with unknown names in many folders, it's impossible to let user register DSN for each of them. Hence I need to find an approach to bypass this step.

I searched similar message on theServerSide.com, it seems a guy tried to do same thing in 2002. There's one reply there: download file from Microsoft and that's it. This person didn't say anything about this magic file and how to deal with it. I checked the MS and didn't find anything useful.

Any more suggestion? Thanks a lot.
Andrew Eccleston
Ranch Hand

Joined: Jul 07, 2004
Posts: 140
Hi Will,

It is possible to specify the database to use in your connection url. It's been a while since I've done this, but it should be something close to this:


Oddly enough, where I found useful information about the parameters like the DBQ and DRIVER (there's a number of other parameters) in the string was at Microsoft. Search for DSN-less connections or ADO connections, both using ASP.

Hope this helps.

andrew


The statement below is true.<br />-------------------------------<br />The statement above is false.
David Ulicny
Ranch Hand

Joined: Aug 04, 2004
Posts: 724
Take look at this, maybe it helps you

http://mdbtools.sourceforge.net/
Will Lee
Ranch Hand

Joined: Mar 16, 2005
Posts: 58
Thanks a lot! I didn't know the terminology "DSN-less connections" Now I googled this keyword and get many links. Hopefully I will work it out soon
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42608
    
  65
There's also Jackcess if a non-JDBC solution is acceptable.


Ping & DNS - my free Android networking tools app
Will Lee
Ranch Hand

Joined: Mar 16, 2005
Posts: 58
Here's the work codes I copied from intnet:


public static final String DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
public static final String DATABASE = "jdbc dbc RIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\\Edu\\Java\\Forum\\DataConnection.mdb";
public static final String USERNAME = "admin";
public static final String PASSWORD = "";

public static void main(String [] args)
{
try
{
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
if (conn != null)
{
DatabaseMetaData meta = conn.getMetaData();
String productName = meta.getDatabaseProductName();
String productVersion = meta.getDatabaseProductVersion();
conn.close();

System.out.println("Driver : " + driver);
System.out.println("Database: " + url);
System.out.println("Username: " + username);
System.out.println("Password: " + password);
System.out.println("Product : " + productName);
System.out.println("Version : " + productVersion);
}
else
{
System.out.println("Database connection was null");
}
}
catch (ClassNotFoundException e)
{
System.err.println("Couldn't load JDBC driver class");
e.printStackTrace();
}
catch (SQLException e)
{
System.err.println("SQL state: " + e.getSQLState());
System.err.println("SQL error: " + e.getErrorCode());
e.printStackTrace();
}
catch (Exception e)
{
e.printStackTrace();
}
 
 
subject: how to connect MS Access directly