Creating a DSN-less connection for MS Access in Java

Question

I am creating a desktop application that should communicate with an MS Access database. Now, if I do not want to register a DSN for the database on all computers that will use the desktop application, I need a way to connect to the database in DSN-less mode.

I searched alot and found useful links on how to create connection strings and based on this I tried to change my program based on this but to no avail. Below is the code below. If I switch the line in getConnection to "jdbc: odbc: sampleDB", it works, but using DSN, and not what I want to achieve.

How to write and use connection string in java to connect to MS Access database without DSN?

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 would also like to note that if someone has an idea of ​​how to achieve what I asked with a DSN connection, I will listen to it with pleasure!

Answer 1

The JDBC connection string starts with jdbc: as:

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

try with:

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

If you configure a DSN, you can connect to it using a simpler connection string: jdbc:odbc:[alias] , for example:

 jdbc:odbc:northwind 

Answer 2

I also had this problem and I tried many suggestions here and in different forums. Finally, I found a fragment from one place that led to a successful connection, and also explains why many of these messages do not work. See http://www.coderanch.com/t/295299/JDBC/databases/jdbc-odbc-DSN-connection-MS

The problem is that after the colon at the end of odbc there should be a semicolon, 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 do not supply DSN, we need to finish; before adding attributes.

I show below the tests that I ran with different connection strings on a 32-bit Windows 7 Ultimate 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. Stmt2 uses the connection string that I found in the documentation for IBM Tivoli. This works because the "MS Access Database" is a valid entry in the ODBC registry as a custom DSN on my computer.