how to connect MS Access directly

[Will Lee]

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.

[Adeel Ansari]

As far as I know there is no way around other then ODBC DSN.

[Sirish Kumar Gongal Reddy]

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]

That is exactly what Will don't want to do

[Sirish Kumar Gongal Reddy]

Hi David Ulicny ,
what about your comment for my above code plz let me know.
Regards,
G Sirish Reddy.,

[David Ulicny]

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]

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]

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:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
 
String url = "jdbc :-o	 dbc:;DRIVER=Microsoft Access Driver (*.mdb);" +
"DBQ=C:\\Temp\\mydatabase.mdb";
 
Connection conn = DriverManager.getConnection(url);
Statement stmt = conn.createStatement();
 
String sql = "select * from mytable;";
 
ResultSet rs = stmt.executeQuery(sql);

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

[David Ulicny]

Take look at this, maybe it helps you

http://mdbtools.sourceforge.net/

[Will Lee]

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]

There's also Jackcess if a non-JDBC solution is acceptable.

[Will Lee]

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();
}