Create an Access database file (.mdb or .accdb) using Java

Question

Currently I have one application in which I am able to access .mdb or .accdb file with JdbcOdbcDriver to append some data.

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        con = DriverManager.getConnection("jdbc:odbc:MsAccessDSN");

but in this, I need to configure System DSN. We need to add new Data Source (Microsoft Access Driver) and then need to provide location of .mdb file. Only then above code will work.

Suppose I want to run my application on other system then I need to do same thing to that computer. If I give my application to the client and he/she don't know how to configure .mdb file. Then my whole effort will waste. So any driver is available by which I create .mdb file by my Java Code and then append all the data into the table of .mdb file. Or is there any other way, where Java code can create .mdb file and able to access this database file.

I tried this code which append data without configuring System DNS:

public class TestMsAccess {

private static Connection con;
private static Statement stm;
private static String tableName = "EmpDetail";
private static int id_is = 2;
private static String name_is = "Employee1";

public static void main(String[] args) throws ClassNotFoundException, SQLException {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    con = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=D:\\MSAccessProject/Employee.mdb", "", "");

    stm = con.createStatement();
    // enter value into table
     String addRow = "INSERT INTO " + tableName  + " VALUES ( "
        + id_is + ", '" 
        + name_is + "')";
     stm.execute(addRow);

     if (con != null) { con.close(); }
     if (stm != null) { stm.close(); }
}

}

But the problem is, this code not create .mdb file automatically but work when I create .mbd file and table before running this code.

score 18 · Accepted Answer · edited Feb 18 '17 at 15:10

Update for Jackcess 2.x: Databases are now created (or opened) using DatabaseBuilder, so to create a new database file we do

import java.io.File;
import java.io.IOException;

import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.Database.FileFormat;
import com.healthmarketscience.jackcess.DatabaseBuilder;

public class JackcessDemoMain {

    public static void main(String[] args) {
        String dbPath = "C:/Users/Public/newDb.accdb";
        // using try-with-resources is recommended to ensure that 
        //   the Database object will be closed properly
        try (Database db = DatabaseBuilder.create(FileFormat.V2010, new File(dbPath))) {
            System.out.println("The database file has been created.");
        } catch (IOException ioe) {
            ioe.printStackTrace(System.err);
        }

    }

}

Original answer for Jackcess 1.x (deprecated):

If you would like to create the “.mdb” file through java, you can use the Jackcess Java library which is one of the pure Java Library for reading from and writing to MS Access databases. Currently supporting versions include 2000-2007 I guess. Please have a look at the below example for better understanding:

Download Jackcess Java library (jackcess-1.2.6.jar) from http://jackcess.sourceforge.net/ and commons-logging-1.1.jar from http://commons.apache.org/logging/download_logging.cgi and commons-lang-2.0.jar from http://www.findjar.com/index.x?query=commons-lang
Add both jars to your classpath.
Try the below code to create a database automatically:

package com.jackcess.lib;


import com.healthmarketscience.jackcess.ColumnBuilder;
import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.Table;
import com.healthmarketscience.jackcess.TableBuilder;
import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.sql.Types;

/**
 *
 * @author sarath_ivan
 */
public class JackcessLibrary {

    private static Database createDatabase(String databaseName) throws IOException {
        return Database.create(new File(databaseName));
    }

    private static TableBuilder createTable(String tableName) {
        return new TableBuilder(tableName);
    }

    public static void addColumn(Database database, TableBuilder tableName, String columnName, Types sqlType) throws SQLException, IOException {
        tableName.addColumn(new ColumnBuilder(columnName).setSQLType(Types.INTEGER).toColumn()).toTable(database);
    }

    public static void startDatabaseProcess() throws IOException, SQLException {
        String databaseName = "C:/Users/compaq/Desktop/employeedb.mdb"; // Creating an MS Access database
        Database database = createDatabase(databaseName);

        String tableName = "Employee"; // Creating table
        Table table = createTable(tableName)
                .addColumn(new ColumnBuilder("Emp_Id").setSQLType(Types.INTEGER).toColumn())
                .addColumn(new ColumnBuilder("Emp_Name").setSQLType(Types.VARCHAR).toColumn())
                .addColumn(new ColumnBuilder("Emp_Employer").setSQLType(Types.VARCHAR).toColumn())
                .toTable(database);

        table.addRow(122875, "Sarath Kumar Sivan","Infosys Limited.");//Inserting values into the table
    }

    public static void main(String[] args) throws IOException, SQLException {
        JackcessLibrary.startDatabaseProcess();
    }
}

thanx a lot ... two error is there while running this code: Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/commons/lang/builder/CompareToBuilder and org.apache.commons.lang.builder.CompareToBuilder — Vinit ..., Mar 05 '12 at 05:16
Oops! One more library needed to run the above code. That is commons-lang-2.0.jar. I forgot to mention about it in the previous post. Extremely sorry for the inconvenience. You can download the commons-lang-2.0.jar from findjar.com/index.x?query=commons-lang. Once it is downloaded, add this jar file to your classpath & run the above code again. This will save your purpose!! — 1218985, Mar 05 '12 at 11:30
As I mention in [my answer](http://stackoverflow.com/a/26302542/2144390), [UCanAccess](http://ucanaccess.sourceforge.net/site.html) is a pure-Java JDBC driver that can also create .mdb and .accdb database files. UCanAccess actually uses Jackcess to read and write the database files, but since UCanAccess is a JDBC driver we can use the more familiar SQL statements (DML and DDL) instead of having to learn the Jackcess API. More details [here](http://stackoverflow.com/q/21955256/2144390). — Gord Thompson, Dec 09 '14 at 19:16

score 2 · Answer 2 · edited May 23 '17 at 12:17

2

Now that the JDBC-ODBC Bridge has been removed from Java (as of Java 8), future readers might be interested in UCanAccess, a free and open-source pure Java JDBC driver for Access databases. UCanAccess includes a newdatabaseversion connection parameter that will create the Access .accdb or .mdb file if it does not already exist.

Sample code:

String dbFileSpec = "C:/Users/Gord/Desktop/myDb.accdb";
try (Connection conn = DriverManager.getConnection(
        "jdbc:ucanaccess://" + dbFileSpec + 
        ";newdatabaseversion=V2010")) {
    DatabaseMetaData dmd = conn.getMetaData();
    try (ResultSet rs = dmd.getTables(null, null, "Clients", new String[] { "TABLE" })) {
        if (rs.next()) {
            System.out.println("Table [Clients] already exists.");
        } else {
            System.out.println("Table [Clients] does not exist.");
            try (Statement s = conn.createStatement()) {
                s.executeUpdate("CREATE TABLE Clients (ID COUNTER PRIMARY KEY, LastName TEXT(100))");
                System.out.println("Table [Clients] created.");
            }
        }
    }
    conn.close();
}

For details on how to set up UCanAccess see

Manipulating an Access database from Java without ODBC

edited May 23 '17 at 12:17

Community

1
1

answered Oct 10 '14 at 15:09

Gord Thompson

116,920
32
215
418

I guess in this example it assumes that myDb.accdb has already some data. Can I create tables and add data to them in freshly new created myDb.accdb file? – Dec 09 '14 at 18:59
@NishantShreshth *"Can I create tables and add data to them"* Yes. The code sample above actually does create a "Clients" table if one does not already exist. That's what the `CREATE TABLE ...` statement does. – Gord Thompson Dec 09 '14 at 19:10
File file = new File("testDb.accdb"); file.createNewFile(); String dbFileSpec = file.getAbsolutePath(); I am creating new file and using all your logic and I am getting exception: net.ucanaccess.jdbc.UcanaccessSQLException: Empty database file. – Dec 09 '14 at 19:13
In my usecase I need to create new files and add data to it. When I am trying to create new file then it throws an UcanaccessSqlException: Empty database file. How can I handle this? – Dec 09 '14 at 19:20
1

@NishantShreshth Don't use `file.createNewFile()`. Just include the path to the file in the connection URL along with the `;newdatabaseversion=V2010` parameter and UCanAccess will create the file for you. If you need further assistance please [ask a new question](http://stackoverflow.com/questions/ask). – Gord Thompson Dec 09 '14 at 19:22

score 2 · Answer 3 · answered Nov 25 '14 at 08:54

download jackcess library

use this library whcih will create .mdb file.Bellow is the code snipet download jackcess libraries from above location. add required jar file in class path.

`

import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.sql.Types;

import com.healthmarketscience.jackcess.ColumnBuilder;
import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.DatabaseBuilder;
import com.healthmarketscience.jackcess.Table;
import com.healthmarketscience.jackcess.TableBuilder;

public class MDBFileGenerator {
    public static void main(String[] args) throws IOException, SQLException {
        Database db = DatabaseBuilder.create(Database.FileFormat.V2000,
                new File("new.mdb"));
        Table newTable = new TableBuilder("NewTable")
                .addColumn(new ColumnBuilder("a").setSQLType(Types.INTEGER))
                .addColumn(new ColumnBuilder("b").setSQLType(Types.VARCHAR))
                .toTable(db);
        newTable.addRow(1, "foo");
    }
}

`

score 1 · Answer 4 · answered Mar 03 '12 at 06:01

You can use the below method instead of configuring System DSN in your machine.

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
connection = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=C:/Users/Desktop/your-database-file.mdb", "", "");

Here "your-database-file.mdb" is your MS-Access file. You can give the full path of your database file in your code to establish the connection. You can also keep the database file in your project(application) folder. In this case you will be able to give your database file along with the application to the client and he/she can use your application without anymore DSN configuration.

Hope this serves your purpose!

Thanks you!

thanx dude ... this code work fine. But the problem is, this code work only when .mdb file present at the location which you give in getConnection. It could not automatically create .mdb file and if you not create .mdb file by yourself then one error is thrown: [Microsoft][ODBC Microsoft Access Driver] Could not find file '(unknown)'. — Vinit ..., Mar 03 '12 at 06:39

score 0 · Answer 5 · answered Mar 03 '12 at 06:01

0

It looks like there is at least one option for connecting directly to .mdb without the JdbcOdbcDriver, that that option is commercial. See here. If the setup is what you're trying to avoid, have you considered using something an embedded database like sqlite?

answered Mar 03 '12 at 06:01

drew

146
2

1

There is also [UCanAccess](http://ucanaccess.sourceforge.net/site.html), which is free. – Gord Thompson Oct 10 '14 at 14:44

Create an Access database file (.mdb or .accdb) using Java

5 Answers5

Linked