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:



Access Contacts table

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:

 

JDBC API References:

 

Related JDBC Tutorials:


About the Author:

is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Java 1.4 and has been falling in love with Java since then. Make friend with him on Facebook and watch his Java videos you YouTube.



Add comment

   


Comments 

#16Piyush Singh Gaharwa2023-07-24 13:59
After downloading Ucanaccess, where I have to put this folder?
Quote
#15GOPAL SHARAN PRASAD2023-06-28 09:02
No suitable driver found for jdbc:ucanaccess://C://users//gopal//Documents//gopal.accdb
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:706)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:252)
at prj.gopal.OracleResult1.main(OracleResult1.java:14)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:251)
at JdbcAccessTest.main(JdbcAccessTest.java:18)
Quote
#14KVSR PAVAN KUMAR2021-01-05 03:22
java.sql.SQLException: No suitable driver found for jdbc:ucanaccess://ssdc.accdb
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:702)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:251)
at JdbcAccessTest.main(JdbcAccessTest.java:18)
Quote
#13Shubham Vilas Tupe2020-12-18 00:09
How to read or access ese file WebCacheV01.dat using java ?
Quote
#12Subhash2020-05-11 01:01
Thank you Sir. Thank a lot.
Quote