Creating a DSN-less connection for MS Access within Java

Soroush Hakami picture Soroush Hakami · Feb 16, 2011 · Viewed 15.1k times · Source

I'm building a desktop app that needs to communicate with a MS Access database. Now, unless I want to register the DSN for the database on every computer that's going to use the desktop app, I need a way to connect to the database in a DSN-less fashion.

I've searched alot and found some useful links on how to create connection strings and based on that I tried modifying my program based on that but without success. The code below fails. If i switch the string in the getConnection to "jdbc:odbc:sampleDB" it works, but that's using DSN and not what I want to achieve.

How do I write and use a connection string in java to make a DSN-less connection to a MS Access database?

private Connection setupConnection() throws ClassNotFoundException,
        SQLException {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con = DriverManager.getConnection("Driver={Microsoft Access Driver (*.mdb)} &_ Dbq=c:\\as\\sampleDB.mdb");
    return con;
}

Addition: I'd also like to point out that if anyone has an idea of a way to achieve what I asked for WITH a DSN-connection I'll gladly listen to it!

Answer

Michał Niklas picture Michał Niklas · Feb 16, 2011

JDBC connection string shouls start with jdbc: like:

jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\\Nwind.mdb

so try with:

   Connection con = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\\as\\sampleDB.mdb");

If you configure DSN then you can connect to it using simplier connect string: jdbc:odbc:[alias], example:

jdbc:odbc:northwind