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?

Comment from Tom B
Posted: December 4, 2014 at 11:59 pm

I don’t want to argue with the author, but Oracle seems to (at least as far as Windows 7 64 bit goes):

http://www.oracle.com/technetwork/java/hotspotfaq-138619.html#64bit_selection

They say in this hotspot VM FQ:

How do I select between 32 and 64-bit operation? What’s the default?

The options -d32 and -d64 have been added to the Java launcher to specify whether the program is to be run in a 32 or 64-bit environment. On Solaris these correspond to the ILP32 and LP64 data models, respectively. Since Solaris has both a 32 and 64-bit J2SE implementation contained within the same installation of Java, you can specify either version. If neither -d32 nor -d64 is specified, the default is to run in a 32-bit environment. Other Java commands (javac, javadoc, etc.) will rarely need to be executed in a 64-bit environment. However, the -d32/-d64 options may be passed to these commands and then on to the Java launcher using the established -J prefix option (eg: -J-d64).
All other platforms (Windows and Linux) contain separate 32 and 64-bit installation packages. If both packages are installed on a system, you select one or the other by adding the appropriate “bin” directory to your path. For consistency, the Java implementations on Linux accept the -d64 option.

————-

The fact the stupid ACE driver requires you to uninstall 32 bit office is a problem since most people’s office products are 32 bit (as per advice from MS on most fronts to use 32 bit versions for compatibility for plug ins, add ons, and all sorts of such like).

In an ideal world, you could get away from using an access file as your data source. Even an SQL Server Express or MySQL DB would be better and is easy to access with their 64 bit ODBC connectors. I know that’s not so much help but MDBs are old file format now and access access components in 64 bit just don’t like coexisting with 32 bit office components. (Frankly, I’d have bashed the head of any dev that thought that was a good idea, but that’s just me).

Write a comment