How to use JDBC-ODBC in a 64-bit JVM with a 32-bit version of Office

When using the JDBC-ODBC bridge in the JDK to access Microsoft Access files, you would set your JDBC class to sun.jdbc.odbc.JdbcOdbcDriver and your JDBC URL, for example, to:

"jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\CodeStreet\sample.accdb"

So far so good, but in case you are running a 64-bit JVM with a 32-bit Microsoft Office installation, your JVM and Access driver architecture don't match  and you would see error messages such as:

java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

or

"The setup routines for the Microsoft Access Driver .. could not be found."

Fortunately, there are now 64-bit Microsoft Access drivers available, but using them in this context is quite tricky. Once you install the drivers, Microsoft Office stops working !

Opening an Excel file, for example, tries to find the 64-bit version of Office, which you don't have :-(

Instead of opening a file, you will see "Configuration Progress" and "Configuring Microsoft Office Professional Plus 2010..." - what the heck ?

But there's a neat little workaround which goes like this: 

1. Download the Microsoft Access drivers  

2. Check this registry key:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Common\FilesPaths 

If if currently contains an entry with mso.dll, you are using Office 64-bit, which is ok. If there is NO mso.dll key then your Office version is 32-bit. 

3. Open a command prompt and install the 64-bit driver in passive mode (it won't let you do this any other way):

    AccessDatabaseEngine_X64.exe /passive

4. If  mso.dll was not in your registry in step 2, then remove this key now from

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Common\FilesPaths 

Microsoft Office (32-bit) should start working again and your 64-bit Access drivers are ready to go.

Good Luck!