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

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.

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

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

  2. 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.

  3. 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!

  4. 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?

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

  6. 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.

  7. 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.

  8. 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

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

  10. 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?

  11. 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

  12. 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? 🙁

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

  14. 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).

  15. 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.

  16. 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.

  17. what is url path for for.Name in window 8? i am facing exception error regardinf “sun.jdbc.odbc.JdbcOdbcDriver”

  18. 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

  19. java.lang.ClassNotFoundException: sun.jdbc.odbc.JdbcOdbcDriver
    still this error occur even i already have follow all these ways

  20. @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!

Leave a Reply

Your email address will not be published. Required fields are marked *