javams-accessucanaccess

Manipulating an Access database from Java without ODBC

I want to manipulate a Microsoft Access database (.accdb or .mdb file) from my Java project. I don't want to use the JDBC-ODBC Bridge and the Access ODBC driver from Microsoft because:

I have seen other answers mentioning a JDBC driver for Access databases named UCanAccess. How can I set up my Java project to use this approach?

(Answers suggesting better ways of working with Access databases from Java would also be most welcome.)

Solution

  • UCanAccess is a pure Java JDBC driver that allows us to read from and write to Access databases without using ODBC. It uses two other packages, Jackcess and HSQLDB, to perform these tasks. The following is a brief overview of how to get it set up.

      Option 1: Using Maven

    If your project uses Maven you can simply include UCanAccess via the following coordinates:

    groupId: net.sf.ucanaccess
    artifactId: ucanaccess

    The following is an excerpt from pom.xml, you may need to update the <version> to get the most recent release:

      <dependencies>
    <dependency>
        <groupId>net.sf.ucanaccess</groupId>
        <artifactId>ucanaccess</artifactId>
        <version>4.0.4</version>
    </dependency>
  </dependencies>

      Option 2: Manually adding the JARs to your project

    As mentioned above, UCanAccess requires Jackcess and HSQLDB. Jackcess in turn has its own dependencies. So to use UCanAccess you will need to include the following components:

    UCanAccess (ucanaccess-x.x.x.jar)
    HSQLDB (hsqldb.jar, version 2.2.5 or newer)
    Jackcess (jackcess-2.x.x.jar)
    commons-lang (commons-lang-2.6.jar, or newer 2.x version)
    commons-logging (commons-logging-1.1.1.jar, or newer 1.x version)

    Fortunately, UCanAccess includes all of the required JAR files in its distribution file. When you unzip it you will see something like

    ucanaccess-4.0.1.jar  
  /lib/
    commons-lang-2.6.jar  
    commons-logging-1.1.1.jar  
    hsqldb.jar  
    jackcess-2.1.6.jar

    All you need to do is add all five (5) JARs to your project.

    NOTE: Do not add loader/ucanload.jar to your build path if you are adding the other five (5) JAR files. The UcanloadDriver class is only used in special circumstances and requires a different setup. See the related answer here for details.

    Eclipse: Right-click the project in Package Explorer and choose Build Path > Configure Build Path.... Click the "Add External JARs..." button to add each of the five (5) JARs. When you are finished your Java Build Path should look something like this

    BuildPath.png

    NetBeans: Expand the tree view for your project, right-click the "Libraries" folder and choose "Add JAR/Folder...", then browse to the JAR file.

    nbAddJar.png

    After adding all five (5) JAR files the "Libraries" folder should look something like this:

    nbLibraries.png

    IntelliJ IDEA: Choose File > Project Structure... from the main menu. In the "Libraries" pane click the "Add" (+) button and add the five (5) JAR files. Once that is done the project should look something like this:

    IntelliJ.png

      That's it!

    Now "U Can Access" data in .accdb and .mdb files using code like this

    // assumes...
//     import java.sql.*;
Connection conn=DriverManager.getConnection(
        "jdbc:ucanaccess://C:/__tmp/test/zzz.accdb");
Statement s = conn.createStatement();
ResultSet rs = s.executeQuery("SELECT [LastName] FROM [Clients]");
while (rs.next()) {
    System.out.println(rs.getString(1));
}

      Disclosure

    At the time of writing this Q&A I had no involvement in or affiliation with the UCanAccess project; I just used it. I later became a contributor to the project.