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

Solution 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.

Solution 2

Now that the JDBC-ODBC Bridge has been removed from Java 8 all of these issues surrounding Access ODBC will increasingly become a thing of the past and a "real" JDBC driver will be required.

As mentioned in an earlier answer, UCanAccess is a free open-source option. It is a pure Java implementation that does not use ODBC at all, so it will work regardless of the platform (Windows, Linux, ...) or architecture (32-bit or 64-bit).

For more information on using UCanAccess, see the related question here.

Comments

[mazaneicha]

Check out this post, it seems like a similar problem stackoverflow.com/questions/3454701/…. Specifilcally, look at the last answer there regarding installing 64-bit MS Access Redistributable pack from Microsoft.

[GameScripting]

Sometimes its also the user account see stackoverflow.com/a/36793611/808723

[kaliatech]

I am confirming what Bruce has said regarding the 64-bit Microsoft Access ODBC driver issues when used with Java. It seems to be very buggy, and for some reason, there is very little information about it on the Internet. I also had problems where column order seemed to matter. I switched back to 32-bit ODBC driver (which also meant switching back to a 32-bit JVM), and everything works fine again.

[abiessu]

This does not answer the question, where the OP explicitly stated that a 64-bit JVM is required in the application.