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

Posted on July 26, 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.

28 thoughts on “Connecting to MS Access Files via JDBC in 64-bit Java”

Erik on July 27, 2011 at 12:00 pm said:

Note that this won’t work on a Mac, since it java impl doesn’t come with the required jdbc driver.E
George Kalfopoulos on October 30, 2011 at 3:23 pm said:

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.
Johnny on November 15, 2011 at 8:08 pm said:

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!
Bill on December 13, 2011 at 7:43 am said:

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?
Levi on August 14, 2012 at 3:35 pm said:

I’m using the 64-bit Eclipse and want to try to use the -D32 flag. Where do I use this in my code?
Jeanne Boyarsky on August 14, 2012 at 6:49 pm said:

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.
Rosemary on August 17, 2012 at 10:38 am said:

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.
Rosemary on August 17, 2012 at 10:52 am said:

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.
Vijay on August 20, 2012 at 12:48 pm said:

code is working Gr8 on both OS i.e 32 bit and 64
Anurag Upadhyay(india) on September 28, 2012 at 2:08 pm said:

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
Daniel on October 1, 2012 at 7:40 am said:

This worked for me and solved a problem i have spent many hours trying to solve, thanks!!
Daniel (argentine) on November 9, 2012 at 12:20 am said:

Thank you so much!!! It works!
Raul on April 16, 2013 at 5:00 pm said:

I have not had to use this, but be aware that Oracle plans on dropping the JDBC-ODBC driver.
Omar on May 13, 2013 at 4:53 pm said:

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?
Humza on July 27, 2013 at 7:11 pm said:

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
ren on October 5, 2013 at 11:16 pm said:

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? 🙁
Fikreyohans on February 14, 2014 at 11:19 am said:

I thank Scott Selikoff (the author) very much.
The material has helped me a lot. keep doing so.
MS Access Driver Version on May 20, 2014 at 11:22 am said:

How do I find out the version of the access driver that I am currently using? If it is 64-bit or 32-bit?
Tom B on December 4, 2014 at 11:59 pm said:

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).
Zershmettaling on February 16, 2015 at 6:32 am said:

You can install the 64 bit driver without uninstalling your 32 bit Office by using command line arguments.
Open CMD in the folder that contains the file “AccessDatabaseEngine_X64.exe” downloaded here: http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255.

Type “AccessDatabaseEngine_X64.exe /log:install.log /quiet /passive”.

Wait a moment and check the created log file if it contains the line
“The property ‘SYS.ERROR.INERROR’ is not equal to ‘1’” -> usually means it was installed successfully.

After that you have to change the following part of your database URL like descripted above from:

“jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)}”

to:

“jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)}”

That works fine for me.
Hope that helps.
Dale Deibert on February 18, 2015 at 9:23 pm said:

This solution has worked very well for me for quite a while. Any ideas on what to do now that sun.jdbc.odbc.JdbcOdbcDriver has been dropped from Java 8? Is there a .jar file somewhere with the sun.jdbc.odbc package.
harish yamgawali on February 20, 2015 at 8:31 am said:

thank you very much Anurag Upadhyay
Anand on June 29, 2015 at 10:31 pm said:

what is url path for for.Name in window 8? i am facing exception error regardinf “sun.jdbc.odbc.JdbcOdbcDriver”
esmail on August 25, 2015 at 4:06 am said:

hi,thank you.it works for me
Guru on January 28, 2016 at 11:36 pm said:

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
String name,pwd;
name=request.getParameter(“name”);
pwd=request.getParameter(“pwd”);

try
{
System.out.println(name);
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
System.out.println(“hi”);
Connection cn=DriverManager.getConnection(“jdbc:odbc:Driver={Microsoft Access Driver (*.mdb,*.accdb)}; DBQ=F:maruthu\\chk.accdb”);
PreparedStatement ps=cn.prepareStatement(“insert into tab values(?,?)”);
ps.setString(1,name);
ps.setString(2, pwd);
ps.executeUpdate();
System.out.println(“Success”);
}

errror :

java.lang.ClassNotFoundException: sun.jdbc.odbc.JdbcOdbcDriver
amn izzat on August 7, 2017 at 12:51 am said:

java.lang.ClassNotFoundException: sun.jdbc.odbc.JdbcOdbcDriver
still this error occur even i already have follow all these ways
A friend on October 27, 2017 at 3:38 am said:

@amn izzat
I assume you installed a Java version higher than 7?
In Java 8 Oracle dropped the ODBC driver out of the standard JRE and you need to add them manually to the class path.

Hope this helps!
Murali N on September 22, 2019 at 3:39 am said:

Use
1. jdbcodbc driver
2. msaccess
3. tomcat 5.0.28
4. deploy war file
5. it works fine on 64 bit machine also on 32 bit machine