Creating a DSN-less connection for MS Access within Java

Question

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!

Solution 1

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

Solution 2

I also had this problem and tried many of the suggestions here and on various forums. Finally, I discovered a snippet from one place which led to success connecting and also explains why many of these posts do not work. See http://www.coderanch.com/t/295299/JDBC/databases/jdbc-odbc-DSN-connection-MS

The issue is that there must be a semicolon after the colon at the end of odbc as in jdbc:odbc:;Driver= . This made sense after reading the Oracle documentation on the JdbcOdbc bridge which states that the syntax is jdbc:odbc:dsn; attributes....... Since we are not supplying a DSN, then we need to end with ; before adding attributes.

I am showing below the tests I ran with different connection strings on a Windows 7 Ultimate 32bit machine:

        driver= (Driver)Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
        //jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=  does lookup to ODBC.ini to find matching driver


            try {
            connstr= "jdbc:odbc:;Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + fileURI;  //64 bit ?? (*.mdb,*.accdb)  
            conn= DriverManager.getConnection(connstr, "", ""); 
            stmt= conn.createStatement();
        }
        catch (Exception e){}
        try {
            connstr= "jdbc:odbc:;Driver={Microsoft Access Driver (*.mdb)};DBQ=" + fileURI;  //64 bit ?? (*.mdb,*.accdb)  
            conn1= DriverManager.getConnection(connstr, "", ""); 
            stmt1= conn1.createStatement();
            dbmeta1=conn1.getMetaData();
        }
        catch (Exception e){}
        try {
            connstr= "jdbc:odbc:MS Access Database;DBQ=" + fileURI;  //64 bit ?? (*.mdb,*.accdb)  
            conn2= DriverManager.getConnection(connstr, "", ""); 
            stmt2= conn2.createStatement();
            dbmeta2=conn2.getMetaData();
        }
        catch (Exception e){}
        try {
            connstr= "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=" + fileURI;  //64 bit ?? (*.mdb,*.accdb)  
            conn3= DriverManager.getConnection(connstr, "", ""); 
            stmt3= conn3.createStatement();
            dbmeta3=conn3.getMetaData();
        }
        catch (Exception e){}

stmt1 and stmt3 are null since the connections are null. stmt and stmt2 work. stmt2 uses a connection string I found in the documentation for IBM Tivoli. It works because "MS Access Database" is a valid title in the ODBC registry as a User DSN on my computer.