How to connect to a 32-bit Access Database from 64-bit JVM?

Question

So far, when I had to connect to an 32-bit Access Database, I simply executed the application using 32-bit JVM. However, I am now developing an application that requires 64-bit JVM, but I still need to connect to an 32-bit Access Database. When I am trying connect, I get this exception:

java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcConnection.initialize(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)

This is my code:

String s = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=" + path;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection(s, user, password);

Answer 1

From my experience

For User (or System ?) ODBC DSN's there are seperate 32 bit and 64 bit definitions. I think you probably have a 32 bit definition, but Java ODBC-Bridge is looking for the 64 bit definition. You can not directly connect to a 32 bit Access ODBC driver from a 64 bit program (you get an error saying this if you try).

While Microsoft distributes 32 bit ODBC Microsoft Access Drivers with Windows (32 & 64 bit), it does not distribute 64 bit MsAccess drivers with Windows 64. There is a a 64 bit Access ODBC Driver available from Microsoft. There are some issues with downloading and installing the 64 bit MsAccess driver

• The Java / Driver only worked intermittently when I tested it with Java 6 (64 bit); I have not tested it with Java 7. With java 6, I think you may be OK if String fields are at the end of SQL select statments. I found some SQL statements worked some did not.
• with the 32 bit driver, you know exactly where there driver is located, with 64 bit driver you do not know where it will be installed. This makes automated installation scripts difficult to write.

• You will also need to create separate 64 bit ODBC definitions.

  ===============================================

On a different note, It may be possible to run a some Database Proxy/Pooling package running in a 32 bit java (and connect via TCP/IP ?). I have Never tried it though.

Java 64 -->> DB Proxy running 32 bit Java DB -->> Ms Access

Database Proxy List: http://www.manageability.org/blog/stuff/jdbc-proxy-drivers

something like SSL-SQL-Proxy Server may work

Good luck, hopefully some one can provide you with a solution

---

Since the original answer, there are 2 JDBC driver's

Open Source: http://ucanaccess.sourceforge.net/site.html

Commercial: http://www.csv-jdbc.com/stels_mdb_jdbc.htm

I have not tried either

---

Edit: 8th May 2014

Looks to be more commercial drivers Easysoft Driver and HXTT Driver

This Article may be useful

---

Edit 6 Jan 2016

As Gord Thompson says; the ODBC-Bridge has been removed from Java 8. The good news is UCanAccess is being actively developed and they seem to making steady progress.