1. Review of the 32-bit Connection Strategy
The well-known syntax for connecting to an Microsoft Access file via JDBC is as follows:
final String fileName = "c:/myDataBase.mdb"; Connection con = null; try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="+fileName; con = DriverManager.getConnection(url,"",""); } catch (Exception e) { // Handle exceptions ... } finally { try { if(con!=null) {con.close();} } catch (Exception e) {} }
If you are using the Sun JDK, then the driver will be available in the classpath automatically. Notice, I make sure to close my connection object in a finally block as all good JDBC developers know to do.
2. Errors in the 64-bit World
Attempting to run the proceeding code returns the following error when using a 64-bit JDK:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
If you would prefer to stick with the 32-bit version of the driver, there are two options available:
- Use the 64-bit Java installation but run java.exe with the “-D32” flag.
- Use a 32-bit Java installation
Both of these solutions limit the amount of memory a Java application can use.
3. Adapting JDBC to 64-bit Java
If you would prefer to use the 64-bit Java, there is a solution, although for some users this may require removing Microsoft Office.
Step #1: Download the Microsoft Access Database Engine 2010 Redistributable, specifically the AccessDatabaseEngine_x64.exe file.
Step #2: Install the Microsoft Access Database Engine. If you are running a 32-bit version of Microsoft Office, you will likely get the following error when you try to install it:
You cannot install the 64-bit version of Office 2010 because you have 32-bit Office products installed.
At this point, you must decide whether or not to uninstall the 32-bit version of Microsoft Office. Newer versions of Office, such as 2010, often contain both 32-bit and 64-bit versions on the installation DVD, although the 32-bit version is used by default when installed via the AutoRun process. In this case, you would first uninstall the 32-bit version of Office. Restart the computer. Then, ignore the AutoRun on the DVD and manually open the setup executable in the x64 directory. After a 64-bit Office version is installed, continue with the Microsoft Access Database Engine installation.
Note: If you are installing a recent 64-bit version of Microsoft Office, you may be able to skip installing the Microsoft Access Database Engine, as it is often included in the Office installation.
If a 64-bit version of Office is not available, then you will unable to run the 32-bit version of Microsoft Office alongside the 64-bit Microsoft Access Database Engine, and must decide which is more important.
Step #3 Update the JDBC code to use the following revised connection string as follows:
final String fileName = "c:/myDataBase.mdb"; Connection con = null; try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ="+fileName; con = DriverManager.getConnection(url,"",""); } catch (Exception e) { // Handle exceptions ... } finally { try { if(con!=null) {con.close();} } catch (Exception e) {} }
After making these changes, you should be able to connect to your Access database in 64-bit Java.