How do I create a connection to MS Access database?

The following example show you how to create a connection to Microsoft Access databases. To allow the database access to be authenticated, the security user account can be added from File -> Info -> Users and Permissions screen.

Just like accessing any other databases in the Java platform we need a JDBC driver. To access Microsoft Access databases we can use UCanAccess, an open-source pure Java JDBC driver for Microsoft Access databases, it allows us to manipulate data in various versions of MS Access databases.

Here what we do in the code snippet below:

  • Prepare USERNAME and PASSWORD that will be used for accessing the database.
  • Define the database JDBC URL which contains the path to MS Access file.
  • Get the connection in try-with-resource statement using the DriverManager.getConnection() method and passes URL, USERNAME, and PASSWORD as arguments.
  • The connection will automatically closed by the try-with-resource when finished.
package org.kodejava.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class MSAccessConnect {
    private static final String USERNAME = "admin";
    private static final String PASSWORD = "admin";

    private static final String URL =
            "jdbc:ucanaccess://C:/Users/wayan/Temp/kodejava.mdb;";

    public static void main(String[] args) throws Exception {
        try (Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            // Do something with the connection here!
            System.out.println("connection = " + connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The connection object printed from the code above:

connection = net.ucanaccess.jdbc.UcanaccessConnection@63376bed[C:\Users\wayan\Temp\kodejava.mdb]

Before JDK 8, the sun.jdbc.odbc.JdbcOdbcDriver driver can be used to connect to Microsoft Access databases. On the example below we can either connect through the DSN created previously on the Windows system, or we can create it in our program as the long URL below.

In Microsoft Access 2003, to allow the database access to be authenticated the security user account can be added from Tools -> Security -> User and Group Accounts menu.

package org.kodejava.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class MSAccessODBCBridge {
    private static final String USERNAME = "admin";
    private static final String PASSWORD = "admin";
    private static final String DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";

    // If you want to use you ODBC DSN
    //private static final String URL = "jdbc:odbc:TestDB";
    private static final String URL =
            "jdbc:odbc:Driver={MICROSOFT ACCESS DRIVER (*.mdb, *.accdb)};" +
            "DBQ=C:/Users/wayan/Temp/kodejava.mdb;";

    public static void main(String[] args) throws Exception {
        Class.forName(DRIVER);
        try (Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            // Do something with the connection here!
            System.out.println("connection = " + connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The connection object printed from the code above:

connection = sun.jdbc.odbc.JdbcOdbcConnection@4ec4f498

Maven Dependencies

<dependency>
  <groupId>io.github.spannm</groupId>
  <artifactId>ucanaccess</artifactId>
  <version>5.1.1</version>
</dependency>