Monday, June 2, 2014

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!
 

No comments:

Post a Comment