Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 494
Vinit ... Avatar asked Mar 03 '12 05:03

Vinit ...


People also ask

How do I make an accdb file?

accdb format. Click File, click Save As, and then click Save Database As. Choose the Access file type, and then click Save As. If any database objects are open when you click Save As, Access prompts you to close them prior to creating the copy.

Can Java connect to Access database?

To connect Java with MS Access, you need a JDBC driver. Although Microsoft do not produce a JDBC driver for MS Access, Easysoft provide two Microsoft Access JDBC drivers. Use these JDBC drivers to provide the connectivity layer between your Java code and MS Access database.

How do I save an Access database as an MDB file?

Open the . accdb file in Access. On the "File" tab of the ribbon, choose "Save & Publish", select the type of . mdb file you want to create (Access 2000 or Access 2002-2003) and click the "Save As" button.


2 Answers

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:

  1. 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
  2. Add both jars to your classpath.
  3. 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();
    }
}
like image 123
1218985 Avatar answered Nov 15 '22 17:11

1218985


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

like image 21
Gord Thompson Avatar answered Nov 15 '22 17:11

Gord Thompson