Manipulating an Access database from Java without ODBC
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. TheUcanloadDriver
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
NetBeans: Expand the tree view for your project, right-click the "Libraries" folder and choose "Add JAR/Folder...", then browse to the JAR file.
After adding all five (5) JAR files the "Libraries" folder should look something like this:
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:
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 have since become a contributor to the project.
Read a MS Access database without OBDC driver
You could try to read directly from the file using a Java API. There are several libraries out there.
You could try out Jackcess. It's free.
Connect to an Access database from Java 8 without using UCanAccess
If you really don't want to use UCanAccess then you'll probably have to buy a third-party library
to replace the JDBC-ODBC Bridge that was removed from Java 8, or
to provide direct JDBC connectivity to the Access database.
However, as Marco indicates in his comment to the question, there could very well be UCanAccess (or HSQLDB) workarounds for your "feature not supported" issues if you care to give us a hint as to what they are.
How to connect java to Ms access Database
you can use ucanacess.jar for connect Ms Aceess database
show some example here http://www.benchresources.net/jdbc-msaccess-database-connection-steps-in-java-8/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class MsAccessDatabaseConnectionInJava8 {
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("net.ucanaccess.jdbc.UcanaccessDriver");
}
catch(ClassNotFoundException cnfex) {
System.out.println("Problem in loading or "
+ "registering MS Access JDBC driver");
cnfex.printStackTrace();
}
// Step 2: Opening database connection
try {
String msAccDB = "D:/WORKSPACE/TEST_WORKSPACE/Java-JDBC/Player.accdb";
String dbURL = "jdbc:ucanaccess://" + msAccDB;
// 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 & 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();
}
}
}
}
Connect to MS access databases in java
when I go to C:\Windows\SysWOW64\obdcad32.exe, I find
| MS Access Database ... Microsoft Access Driver (*.mdb, *.accdb)
So it seems that I have a 64bit version of ODBC
The folder name "SysWOW64" can be a bit misleading. It contains the components for the 32-bit "WOW" subsystem (i.e., "[32-bit]Windows On Windows[64]").
So, "SysWOW64\obdcad32.exe" is actually the 32-bit ODBC administrator and you in fact have the 32-bit version of the Access "ACE" ODBC driver installed. Therefore you need to run your application under a 32-bit version of the JRE (Java Runtime Environment) in order to use that ODBC driver.
Also, bear in mind that the JDBC-ODBC Bridge was removed from Java 8, so your JRE must be for Java 7 or earlier. (For Java 8 and later, consider using the UCanAccess JDBC driver. Details here.)
Related Topics
Access Private Field of Another Object in Same Class
Infinite Recursion With Jackson Json and Hibernate JPA Issue
How to Best Position Swing Guis
Java: Maintaining Aspect Ratio of Jpanel Background Image
What Causes Error "No Enclosing Instance of Type Foo Is Accessible" and How to Fix It
Java - Sending Http Parameters Via Post Method Easily
Generating All Permutations of a Given String
How Are Anonymous Inner Classes Used in Java
How to Solve "Java.Lang.Noclassdeffounderror"
How to Read a Large Text File Line by Line Using Java
Java: Splitting a Comma-Separated String But Ignoring Commas in Quotes
How to Read Xml Using Xpath in Java
Setting the Default Java Character Encoding
Retain Precision With Double in Java
Mocking Static Methods With Mockito
How to Really Read Text File from Classpath in Java
Run Java Class File from PHP Script on a Website
Non-Static Variable Cannot Be Referenced from a Static Context