Java and Microsoft Access via Jackcess: How to Connect MDB or ACCDB, Get Data from Table and Display It in JTable

• Download Java_MDB_NetBeans - 1.3 MB
• Download Java_ACCDB_NetBeans - 1.3 MB

Introduction

This "simple-sample" demonstrates:

• how to open MDB/ACCDB, create it if not exists, and create table if not exists
• how to get list of its tables to JComboBox
• how to connect a table and show data from table in JTable

Preparation

Jackcess is pure Java library, It doesn't requires any third-party programs and not-Java libraries (i.e. Microsoft Access, DAO, Microsoft Jet 4.0, etc.)

You should only:

• download Jackcess
• download Apache Common Lang Library (2.6 version, not 3.*, important!)
• download Apache Common Logging Library (1.2 version)
• add all these libraries to project

Hint: All needed libs included in two samples are attached to this tip. See the links at the top of this post.

Code

In JFrame Class

private Connection connect = null;
private Statement statement = null;

Open DB, or Create If It Doesn't Exist

try {
    File dbFile = new File("testDB.accdb");
    if(dbFile.exists() && !dbFile.isDirectory()) {
db = DatabaseBuilder.open(dbFile);
    } else {
db = DatabaseBuilder.create(Database.FileFormat.V2007, dbFile);
    }
} catch (IOException e) {
    JOptionPane.showMessageDialog(this,
        "Can't create or open file \"testDB.accdb\". 
        Check if it permitted by security settings of path/file.\nMore info:\n" +
        e,
        "Error", JOptionPane.ERROR_MESSAGE);
}

Create "Table 1" Table If It Does Not Exist

try {
    if (!db.getTableNames().contains("Table 1")) {
        Table tblNew = new TableBuilder("Table 1")
            .addColumn(new ColumnBuilder("id", DataType.LONG)
                .setAutoNumber(true))
            .addColumn(new ColumnBuilder("name", DataType.TEXT))
            .addColumn(new ColumnBuilder("age", DataType.INT))
            .addIndex(new IndexBuilder(IndexBuilder.PRIMARY_KEY_NAME)
            .addColumns("id").setPrimaryKey())
        .toTable(db);

        // Fill table by data
        tblNew.addRow(Column.AUTO_NUMBER, "John", 27);
        tblNew.addRow(Column.AUTO_NUMBER, "Peter", 43);
    }
} catch (Exception e) { }

Get All Tables Names and Add Them to combobox

try {
    Set<String> tables = db.getTableNames();
    
    for (String t : tables) {
        jComboBox1.addItem(t);
    }
} catch (Exception e) { }

Load Data from User-selected Table and Show it in JTable

try {
    tbl = db.getTable(jComboBox1.getSelectedItem().toString());
    
    tm = (DefaultTableModel)jTable1.getModel();
    
    // Clear TableModel before filling
    tm.setColumnCount(0);
    tm.setRowCount(0);
    
    // Add all columns from table
    for (Column col : tbl.getColumns()) {
        tm.addColumn(col.getName());
    }
    
    // Add all rows from table
    for (Row row : tbl) {
        tm.addRow(row.values().toArray());
    }
} catch (Exception e) { }