Java 64-bit JDBC-ODBC driver issues

Question

I have a program that, when compiled using the 32 bit JVM works fine, but has issues if I try to use the 64 bit JVM. The message I'm getting is: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

I'm trying to connect to Excel and SQL Server databases using code like the following:

String file = directory + "/fileName.xlsm";

String connectStr= "jdbc:odbc:DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ="  + file +  ";READONLY=false";

try {
    Class.forName(getDriver());
    gConnection = DriverManager.getConnection(connectStr);
    //do stuff with connection
}

When I tried to check the Driver Manager it didn't seem to have 64 bit version of the drivers. Any way to fix this easily and be able to connect using 64-bit drivers without manually changing settings on the computer (as this program will be distributed across multiple computers and I don't want to have to download a driver separately for ever computer that wants to run it)? Also, is it any more efficient to connect using 64-bit drivers, or are 32-bit ones just as good/fast (I do have very large data sets, so small differences would make a difference)?

Answer 1

64-bit applications cannot use 32-bit ODBC drivers, and vice versa, and that is why you are getting that error message. You can verify what ODBC drivers are available by running the 32-bit (C:\Windows\SysWow64\odbcad32.exe) and 64-bit (C:\Windows\System32\odbcad32.exe) ODBC Data Source administrator (Drivers tab) respectively - on a 64-bit system of course. The naming is confusing at first.

An application I worked with had a similar problem - the Access/Excel ODBC driver was 32-bit-only, meaning when run in 64-bit our application could not handle opening Excel or Access database files. We eventually switched to using the Apache POI library which is a Java library that can read/write Excel and other Office documents directly. You may want to consider giving that a try, though switching would involve some amount of work.

At the time, Office 2010 wasn't out yet. I didn't realize they created a 64-bit ODBC driver in Office 2010 and will have to see if that is a legitimate option now... even if it is I don't like relying on ODBC in a Java application.