UCanAccess: Add column to existing table

Question

I've got a working Java project which uses an Access .accdb database to store data. I am working on an update for my program to provide more features for the user. For this to work I need to add a column to an existing table filled with data. As I researched I found out that UCanAccess could not support

ALTER TABLE TableName ADD ColumName DataType

This is unfortunate, but I understand that since the low level drivers don't support it, UCanAccess can't support it either.

I then found this workaround:

How to alter table using UCanAccess

but this won't work for me either, because 1. I'm using .accdb and 2. I can't be sure the users have Microsoft Access installed.

Then I had the idea to clone the current Table with 1 extra column, clone all data into it, drop the old one and rename the new one to the Name of the old one. But i don't know how to do this.

Does anyone know a better way or know how to implement my idea?

Answer 1

Jackcess 2.1.5 added the ability to add a new column to an existing table. There are plans to expand DDL support in UCanAccess to enable ALTER TABLE, but in the meantime if you update your project to use Jackcess 2.1.5 or later (in place of the earlier Jackcess version in the UCanAccess lib/ folder) then you can add your column like so:

// use the Jackcess (2.1.5 or later) API directly 
//   to add a column to an existing table
//
// Note: Close any open UCanAccess connection first.
//
com.healthmarketscience.jackcess.Database db = 
        com.healthmarketscience.jackcess.DatabaseBuilder.open(new File(dbFileSpec));
new com.healthmarketscience.jackcess.ColumnBuilder("newCol")
        .setType(com.healthmarketscience.jackcess.DataType.LONG)
        .addToTable(db.getTable("TableName"));
db.close();

Update: January 2017

UCanAccess versions 4.0.0 and above now support ALTER TABLE, e.g.,

Statement stmt = conn.createStatement();
stmt.execute("ALTER TABLE TableName ADD COLUMN newCol LONG");