Java JDBC Example Connect to Microsoft Access Database

This JDBC tutorial guides you how to develop a Java program that connects to a Microsoft Access Database. In the early days of JDBC, you can connect to an Access database via JDBC ODBC driver provided by JDK. However JDBC ODBC driver is no longer supported so you need to use a third-party JDBC driver for Microsoft Access. And your Java code still uses JDBC API as normal.

1. Java JDBC Driver for Microsoft Access Database

There are several third-party JDBC drivers out there for Microsoft Access database, and we recommend UCanAccess - a pure Java JDBC Driver for Access that allows Java developers and JDBC client programs to read/write Microsoft Access databases. UCanAccess supports various Access formats: 2000, 2002/2003, 2007, 2010/2013/2016 (Access 97 is supported for read-only).

UCanAccess is open-source and implemented entirely in Java so it can be used across platforms (Windows, Mac, Linux…). It also provides Maven dependencies so you can integrate it in your existing projects quickly.

To use UCanAccess JDBC Driver for Access, add the following dependency information in your project’s pom.xml file:

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

In case you don’t use Maven, you have to download UCanAccess distribution and add the following JAR files to the classpath:

• ucanaccess-4.0.4.jar
• hsqldb-2.3.1.jar
• jackcess-2.1.11.jar
• commons-lang-2.6.jar
• commons-logging-1.1.3.jar

The version numbers here may differ than the latest versions you downloaded. Now, let’s see how to write a simple Java program to read/write a Microsoft Access database.

2. Java JDBC Example with Access Database

Suppose that we have an Access Database 2007 contains a table Contacts with the following fields:

The database file is located at e:\Java\JavaSE\MsAccess\Contacts.accdb. - This path will be used in database URL. We will write a Java program that uses the UCanAccess JDBC driver to connect to this database, insert a row and select all rows from the table Contacts.

You can use JDBC API as normal (see Connect to a database with JDBC). The differences lie in the database URL and Access-specific SQL syntax you can use. For example, you need to construct the database URL to include path of the Access database file like this:

String databaseURL = "jdbc:ucanaccess://e://Java//JavaSE//MsAccess//Contacts.accdb";

And here is code of the example program:

package net.codejava.jdbc;

import java.sql.*;

/**
 * This program demonstrates how to use UCanAccess JDBC driver to read/write
 * a Microsoft Access database.
 * @author www.codejava.net
 *
 */
public class JdbcAccessTest {

	public static void main(String[] args) {
		
		String databaseURL = "jdbc:ucanaccess://e://Java//JavaSE//MsAccess//Contacts.accdb";
		
		try (Connection connection = DriverManager.getConnection(databaseURL)) {
			
			
			String sql = "INSERT INTO Contacts (Full_Name, Email, Phone) VALUES (?, ?, ?)";
			
			PreparedStatement preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, "Jim Rohn");
			preparedStatement.setString(2, "rohnj@herbalife.com");
			preparedStatement.setString(3, "0919989998");
			
			int row = preparedStatement.executeUpdate();
			
			if (row > 0) {
				System.out.println("A row has been inserted successfully.");
			}
			
			sql = "SELECT * FROM Contacts";
			
			Statement statement = connection.createStatement();
			ResultSet result = statement.executeQuery(sql);
			
			while (result.next()) {
				int id = result.getInt("Contact_ID");
				String fullname = result.getString("Full_Name");
				String email = result.getString("Email");
				String phone = result.getString("Phone");
				
				System.out.println(id + ", " + fullname + ", " + email + ", " + phone);
			}
			
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}
}

As you can see, this example looks like trivial JDBC code, the only difference lies in the database URL that needs to include path to the Access database file.

If you want to see the coding in action, I recommend to watch the video version of this article below:

References:

• UCanAccess Home Page
• Download UCanAccess JDBC Driver for Microsoft Access

JDBC API References:

• DriverManager Class Javadoc
• Connection Interface Javadoc

Related JDBC Tutorials:

• JDBC CRUD Tutorial
• JDBC Transaction Tutorial
• How to Read Database Meta Data in JDBC
• How to call stored procedure with JDBC

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.