How to ALTER TABLE using UCanAccess

Question

I am using UCanAccess JDBC-driver (version 3.0.3.1) to connect to the mdb-file. And I need to add the column to existing table. The problem is that the statement

ALTER TABLE TEmployee ADD COLUMN NotificationsEnabled BINARY

throws the exception:

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.x.x Feature not supported yet.

And it seems like there is no newer versions of UCanAccess.

What can I do in such a situation? I don`t want to use the ODBC driver for many reasons (described here - Manipulating an Access database from Java without ODBC)

The only solution that I see is to create the copy of the table (e.g. TEmployeeBackup) to save the data, then drop and recreate the original table with new field, and then move the data back from TEmployeeBackup to TEmployee. But this solution seems awful to me.

Answer 1

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");

Answer 2

Update: January 2017

UCanAccess now supports ALTER TABLE. See my other answer to this question.

---

(Previous outdated answer.)

If your Java app is running under Windows then you can use the following workaround. It creates a little VBScript and invokes CSCRIPT.EXE to run it

String dbFileSpec = "C:\\Users\\Public\\mdbTest.mdb";

// write a temporary VBScript file ...
File vbsFile = File.createTempFile("AlterTable", ".vbs");
vbsFile.deleteOnExit();
PrintWriter pw = new PrintWriter(vbsFile);
pw.println("Set conn = CreateObject(\"ADODB.Connection\")");
pw.println("conn.Open \"Driver={Microsoft Access Driver (*.mdb)};Dbq=" + dbFileSpec + "\"");
pw.println("conn.Execute \"ALTER TABLE TEmployee ADD COLUMN NotificationsEnabled YESNO\"");
pw.println("conn.Close");
pw.println("Set conn = Nothing");
pw.close();

// ... and execute it
Process p = Runtime.getRuntime().exec("CSCRIPT.EXE \"" + vbsFile.getAbsolutePath() + "\"");
p.waitFor();
BufferedReader rdr = 
        new BufferedReader(new InputStreamReader(p.getErrorStream()));
int errorLines = 0;
String line = rdr.readLine();
while (line != null) {
    errorLines++;
    System.out.println(line);  // display error line(s), if any
    line = rdr.readLine();
}
if (errorLines == 0) {
    System.out.println("The operation completed successfully.");
}

Notes:

1. The above code will work for updating an .mdb file if the Java app is running under a 32-bit JVM. If you need to update an .accdb file or if the Java app is running under a 64-bit JVM then the appropriate version of Microsoft's Access Database Engine (32-bit or 64-bit, same as the JVM) will have to be installed and you will need to use Driver={Microsoft Access Driver (*.mdb, *.accdb)}.

2. This workaround uses ODBC but it does not use Java's JDBC-ODBC Bridge, so it will work with Java 8.

Answer 3

Ucanaccess can't support this very requested feature until the underlying jackcess library doesn't support it. We (Ucanaccess team) could automate the above mentioned steps, but for the same reason, we can't create fk and so, in many cases, reproduce an exact copy of an original table. While altering a table, we would lose the referential integrity constraints... so, for now, it's better to do nothing. So sorry, no solution currently.