32 bit, 64 bit confusions: ODBC connection

Often I have seen people get confused in an mixed 32 bit, 64 bit environment. Mostly people will have 64 bit OS like Windows 7, 64 bit with 32 bit applications like MS office 2007. Like recently I found suddenly a simple JDBC code (using JDBC-ODBC bridge) which was reading Excel file, started failing in a new environment. Just listing down things to keep in mind:

- Check if you have 32 bit Java or 64 bit Java?
- Check if you have 32 bit ODBC drivers or 64 bit ODBC drivers.
- Finally check if you are using JDBC connection URL for the version of ODBC drivers you have.

So, in 32 bit environment:
run C:\windows\sysWOW64\odbcad32.exe and see if the drivers are present for excel or not.
If not then install AccessDatabaseEngine.exe from Microsoft Download Center.

Now the connection URL in the JDBC code will be:
jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=<Path to .xls file>

And in a 64 bit environment:
Run C:\windows\system32\odbcad32.exe and see if the drivers are present.
If not then install AccessDatabaseEngine_x64.exe from the link mentioned above.

Now the connection URL will be:
jdbc:odbc:DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=<Path to .xls file>

Sample code:

Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );

//using DSN-less connection in 32 bit env
con = DriverManager.getConnection( "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:/Abhinay/test.xls")

//using DSN-less connection in 64 bit env

//con = DriverManager.getConnection( "jdbc:odbc:DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:/Abhinay/test.xls");

stmnt = con.createStatement();
String query = "select * from [Sheet1$];";
ResultSet rs = stmnt.executeQuery( query );