Adding an AutoNumber primary key field to an existing table

Recent enhancements in data-definition language (DDL) handling are a welcome addition. However, there is still one case that UCanAccess DDL does not cover effectively: adding an AutoNumber primary key column to an existing table.

Yes, we can now use

st.execute("ALTER TABLE [Table1] ADD COLUMN [ID] COUNTER DEFAULT 0");

and that will in fact add an AutoNumber [ID] column to the table [Table1], but that column will be empty. Furthermore, it is not obvious how we might fill that column with sequential values, given the limitations on updatable resultsets caused by the way UCanAccess interacts with HSQLDB.

Fortunately, the solution is simple and very fast: just use Jackcess to add the AutoNumber primary key field. Since Jackcess is one of UCanAccess' dependencies it is already available to us, so all we need to do is something like this:

import java.io.File;
import com.healthmarketscience.jackcess.ColumnBuilder;
import com.healthmarketscience.jackcess.DataType;
import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.DatabaseBuilder;
import com.healthmarketscience.jackcess.IndexBuilder;
import com.healthmarketscience.jackcess.Row;
import com.healthmarketscience.jackcess.Table;

// ...

String dbPath = "C:/Users/Public/example.accdb";
DatabaseBuilder dbb = new DatabaseBuilder(new File(dbPath))
        .setAutoSync(false);
try (Database db = dbb.open()) {
    Table tbl = db.getTable("Table1");
    new ColumnBuilder("ID", DataType.LONG)
            .setAutoNumber(true).addToTable(tbl);
    tbl.setAllowAutoNumberInsert(true);
    int i = 1;
    for (Row r : tbl) {
        r.put("ID", i++);
        tbl.updateRow(r);
    }
    new IndexBuilder("PrimaryKey").addColumns("ID")
            .setPrimaryKey().addToTable(tbl);
}

Note that it would be a very good idea to NOT have a UCanAccess connection open to that database when running the above code.