Connecting to MS Access Files via JDBC in 64-bit Java

Main menu:

Topics

Recent Posts

Blog

July 2011
M T W T F S S
« Jun   Aug »
 123
45678910
11121314151617
18192021222324
25262728293031

Past Posts

Java/Java EE

JDBC

Other

Connecting to MS Access Files via JDBC in 64-bit Java

July 26th, 2011 by Scott Selikoff
In 32-bit Java, the technique for connecting to a Microsoft Access file via JDBC connection is well-known and available as part of the Sun JDK. There are a number of errors, though, if you attempt to use a 64-bit version of Java that are not as well documented. This article points out some of those issues and a working strategy for how to successfully connect to a Microsoft Access file via JDBC in 64-bit Java.

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.

Comments

Comment from Erik
Posted: July 27, 2011 at 12:00 pm

Note that this won’t work on a Mac, since it java impl doesn’t come with the required jdbc driver.E

Comment from George Kalfopoulos
Posted: October 30, 2011 at 3:23 pm

Now that was one very useful finding for me! I had a similar problem and I ended up using the -D32 flag. I was unaware that there was a 64bit solution to the problem (though in my case it would not work due to the 32bit Office). Many thanks for the post.

Comment from Johnny
Posted: November 15, 2011 at 8:08 pm

Thanks dude, you saved my ass! I’m working on a project for a client right now and I was facing that 64 bit error for 3 hours. After reading through your post, it solved my problem =) You deserve a high five!

Comment from Bill
Posted: December 13, 2011 at 7:43 am

Very helpful (hard to find the correct 64bit connection string anywhere). Just a note – after connection I was getting: [Microsoft][ODBC Driver Manager] Invalid string or buffer length which I haven’t found solutions for. I got around it by changing the order in which I retrieved fields from the resultset rows. (I retrieved all the strings first, then the ints and doubles). Wierd, huh?

Comment from Levi
Posted: August 14, 2012 at 3:35 pm

I’m using the 64-bit Eclipse and want to try to use the -D32 flag. Where do I use this in my code?

Comment from Jeanne Boyarsky
Posted: August 14, 2012 at 6:49 pm

Levi,
I haven’t tried running in 32 bit mode. I suggest asking this question in a forum like coderanch where more people will see it.

Comment from Rosemary
Posted: August 17, 2012 at 10:38 am

When compiling do we need to use the -D32 flag or is it only when running the application?

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.

Comment from Rosemary
Posted: August 17, 2012 at 10:52 am

When compiling do we need to use the -D32 flag or is it only when running the application?

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.

Comment from Vijay
Posted: August 20, 2012 at 12:48 pm

code is working Gr8 on both OS i.e 32 bit and 64

Comment from Anurag Upadhyay(india)
Posted: September 28, 2012 at 2:08 pm

jdbc-odbc connection for window 7 64 bit machine..
1 . right click Data source (ODBC)..go to properties change the folloing thing

target [ %SystemRoot%\SysWOW64\odbcad32.exe ]
start in : [ %SystemRoot%\System32 ]

press enter and continue as admin

Comment from Daniel
Posted: October 1, 2012 at 7:40 am

This worked for me and solved a problem i have spent many hours trying to solve, thanks!!

Comment from Daniel (argentine)
Posted: November 9, 2012 at 12:20 am

Thank you so much!!! It works!

Comment from Raul
Posted: April 16, 2013 at 5:00 pm

I have not had to use this, but be aware that Oracle plans on dropping the JDBC-ODBC driver.

Comment from Omar
Posted: May 13, 2013 at 4:53 pm

A very useful solution it work for me, thank you very much. Excuse but in the post you talk about the -d32 flag, how can you use that?, where do you set up that?

Comment from Humza
Posted: July 27, 2013 at 7:11 pm

Hi I’m using Netbeans 7.2.1 running Java 64 bit on a windows 7 64 bit but office 2010 32 bit. i copied your solution #1 & #3 yet still no joy. Any advice

Comment from ren
Posted: October 5, 2013 at 11:16 pm

I have netbeans 7.3.1 ang i have 64bit windows 7 OS and ms access 64 bit but i have this error

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

how can i fix this? :(

Comment from Fikreyohans
Posted: February 14, 2014 at 11:19 am

I thank Scott Selikoff (the author) very much.
The material has helped me a lot. keep doing so.

Comment from MS Access Driver Version
Posted: May 20, 2014 at 11:22 am

How do I find out the version of the access driver that I am currently using? If it is 64-bit or 32-bit?

Write a comment