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.