Connect to access using java

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:

 net.sf.ucanaccess ucanaccess 4.0.4 

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

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";

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

Nam Ha Minh 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

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(Driv erManager.java:251)
at JdbcAccessTest.main(JdbcAccessTest.java:18)

java.sql.SQLException: No suitable driver found for jdbc:ucanaccess://ssdc.accdb
at java.sql/java.sql.DriverManager.getConnection(Driv erManager.java:702)
at java.sql/java.sql.DriverManager.getConnection(Driv erManager.java:251)
at JdbcAccessTest.main(JdbcAccessTest.java:18)

Источник

How to Connect Java (JDBC) with MS Access Database

In this tutorial I will teach you how to connect java (jdbc) with ms access database.

MS Access is a part of Microsoft Office and used as database management system (dbms).

For making a new database go to MS Access > Blank Database. Give a name to database and click on Create button to create the database.

Below example shows jdbc ms access database connectivity.

How to Connect Java (JDBC) with MS Access Database

JDK 1.8

If you are using jdk 1.8 then you will get ClassNotFoundException. Just read below article to solve the error.

Above program is really easy to understand. You can ask your queries in comment section.

Related Posts

How to Connect Java (JDBC) with MySQL or Oracle Database

Solve java.lang.ClassNotFoundException: sun.jdbc.odbc.JdbcOdbcDriver Error

Save and Retrieve Image from MySQL Database Using Servlet and JSP

Servlet Registration Form with MySQL Database Example

3 thoughts on “How to Connect Java (JDBC) with MS Access Database”

getting following error pls fix that for me.

java.lang.ClassNotFoundException: sun.jdbc.odbc.JdbcOdbcDriver
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Unknown Source)
at com.JDBCMSAccess.main(JDBCMSAccess.java:13)

java.lang.ClassNotFoundException: sun.jdbc.odbc.JdbcOdbcDriver
at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:602)
at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:178)
at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:522)
at java.base/java.lang.Class.forName0(Native Method)
at java.base/java.lang.Class.forName(Class.java:340)
at GUI.(GUI.java:71)
at GUI.main(GUI.java:108)

I got this error. i am using JDK 1.14.0

Источник

2. Java JDBC application :

2.2 JDBC program to connect and query MS Access database/table :

• Once we are ready with above listed things
• Then we can go ahead and code an example to connect MS Access database
• Finally querying database

MsAccessDatabaseConnectionExample.java

package in.bench.resources.msaccess.db.example; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class MsAccessDatabaseConnectionExample < public static void main(String[] args) < // variables Connection connection = null; Statement statement = null; ResultSet resultSet = null; // Step 1: Loading or // registering Oracle JDBC driver class try < Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); >catch(ClassNotFoundException cnfex) < System.out.println("Problem in loading" + " MS Access JDBC driver"); cnfex.printStackTrace(); >// Step 2: Opening database connection try < String msAccessDBName = "D:\\WORKSPACE" + "\\TEST_WORKSPACE\\Java-JDBC\\Player.accdb"; String dbURL = "jdbc:odbc:Driver=" + ";" + "DBQ=" + msAccessDBName + ";DriverID=22;READONLY=true"; // Step 2.A: Create and // get connection using DriverManager class connection = DriverManager.getConnection(dbURL); // Step 2.B: Creating JDBC Statement statement = connection.createStatement(); // Step 2.C: Executing SQL and // retrieve data into ResultSet resultSet = statement .executeQuery("SELECT * FROM PLAYER"); System.out.println("ID\tName\t\t\tAge\tMatches"); System.out.println("==\t================\t===\t======="); // processing returned data and printing into console while(resultSet.next()) < System.out.println(resultSet.getInt(1) + "\t" + resultSet.getString(2) + "\t" + resultSet.getString(3) + "\t" + resultSet.getString(4)); >> catch(SQLException sqlex) < sqlex.printStackTrace(); >finally < // Step 3: Closing database connection try < if(null != connection) < // cleanup resources, once after processing resultSet.close(); statement.close(); // and then finally close connection connection.close(); >> catch (SQLException sqlex) < sqlex.printStackTrace(); >> > >

Output:

ID Name Age Matches == ================ === ======= 1 Sachin Tendulkar 43 200 2 Shane Warne 45 145 3 Kevin Pietersen 36 104 4 Shahid Afridi 36 27 5 Brian Lara 46 131 6 Graeme Smith 36 117 7 Mahela Jayawardene 38 145

2.3 Download :

Related Articles :

• Java – Introduction to JDBC
• Java – JDBC Driver types
• Java – Core JDBC components
• Java – JDBC Driver list for all leading database
• Java – JDBC connection steps
• Java – An example to connect MySQL database
• Java – An example to connect Oracle database
• Java – An example to connect MS Access database
• Java 8 – An example to connect MS Access database in Java 8
• Java – JDBC program to connect IBM DB2 database running on Mainframe z/OS system
• Java – Creating database using JDBC Statement interface
• Java – Droping database using JDBC Statement interface
• Java – Creating a table using JDBC Statement interface
• Java – Inserting a record using JDBC Statement interface
• Java – Getting all list of records using JDBC Statement interface
• Java – Getting single record using JDBC Statement interface
• Java – Updating a record using JDBC Statement interface
• Java – Deleting a record using JDBC Statement interface
• Java – Dropping a table using JDBC Statement interface
• Java – Batch update using JDBC Statement interface
• Java – Batch insert using JDBC Statement interface
• Java – Creating a table using JDBC PreparedStatement interface
• Java – Inserting a record using JDBC PreparedStatement interface
• Java – Getting all list of records using JDBC PreparedStatement interface
• Java – Getting single record using JDBC PreparedStatement interface
• Java – Updating a record using JDBC PreparedStatement interface
• Java – Deleting a record using JDBC PreparedStatement interface
• Java – Batch update using JDBC PreparedStatement interface
• Java – Batch insert using JDBC PreparedStatement interface
• Java – Calling Stored Procedure using JDBC CallableStatement interface
• Java – Calling Stored Function using JDBC CallableStatement interface
• Java – Calling Stored Procedure using JDBC CallableStatement interface with Batch execution
• Java – Transaction handling using JDBC Statement interface
• Java – Transaction handling using JDBC PreparedStatement interface
• Java – Integration with Spring framework (Spring JDBC)
• Java – Where clause example using JDBC Statement interface
• Java – Like clause example using JDBC Statement interface
• Java – Order by clause example using JDBC Statement interface
• Java – Metadata of database using JDBC DatabaseMetaData interface
• Java – Metadata of Resultset using JDBC ResultSetMetaData interface
• Java – Interview question and answer on JDBC

References :

Happy Coding !!
Happy Learning !!

Источник