TL;DR: What Database.FileFormat constant should I use for an MS Access 2000-2003 database, when creating the Database object?
I have built a SAMBA test application using jCIFS. It allows me to create/overwrite files if given the correct authentication credentials, regardless of on which PC in the domain I use it.
I also have an application that uses uCanAccess/jackcess to connect to an MDB on a network share. However (from what I understand), it uses the credentials of the logged-in user, a number of whom have read-only access. Only system/network administrators have write permission.
The database in question is not password-protected. (I don't need to enter a password when opening it.)
My intention is to have the app ask for the administrator's Samba credentials before it writes to the DB, using those in the uCanAccess connection, so that it doesn't throw a java.nio.channels.NonWritableChannelException, as per the below stack trace:
java.nio.channels.NonWritableChannelException
at sun.nio.ch.FileChannelImpl.write(FileChannelImpl.java:747)
at com.healthmarketscience.jackcess.impl.PageChannel.writePage(PageChannel.java:310)
at com.healthmarketscience.jackcess.impl.PageChannel.writePage(PageChannel.java:247)
at com.healthmarketscience.jackcess.impl.TableImpl.writeDataPage(TableImpl.java:1980)
at com.healthmarketscience.jackcess.impl.TableImpl.addRows(TableImpl.java:2229)
at com.healthmarketscience.jackcess.impl.TableImpl.addRow(TableImpl.java:2067)
at net.ucanaccess.converters.UcanaccessTable.addRow(UcanaccessTable.java:44)
at net.ucanaccess.commands.InsertCommand.insertRow(InsertCommand.java:101)
at net.ucanaccess.commands.InsertCommand.persist(InsertCommand.java:148)
at net.ucanaccess.jdbc.UcanaccessConnection.flushIO(UcanaccessConnection.java:315)
at net.ucanaccess.jdbc.UcanaccessConnection.commit(UcanaccessConnection.java:205)
at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:217)
at net.ucanaccess.jdbc.Execute.execute(Execute.java:46)
at net.ucanaccess.jdbc.UcanaccessPreparedStatement.execute(UcanaccessPreparedStatement.java:228)
at myapp.db.Digger.addTransaction(Digger.java:993)
at myapp.tasks.TransactionRunnable.run(TransactionRunnable.java:42)
at java.lang.Thread.run(Thread.java:745)
Update: I have tried using the smbFileChannel class by Gord Thompson and J. T. Alhborn, shown here. My code, based off the main class shown in that answer, looks like this:
// Ask the user for login credentials and the path to the database
String smbURL = (chosenDir.endsWith("/") ? chosenDir : chosenDir + '/')
+ dbName;
System.out.println("DB Path to use for URL: " + smbURL);
URL u = new URL(smbURL);
try (
// construct the SMB DB URL
SmbFileChannel sfc = new SmbFileChannel(smbURL);
Database db = new DatabaseBuilder().setChannel(sfc)
.setFileFormat(Database.FileFormat.GENERIC_JET4).create();
) {
// Model the table
Table tbl = new TableBuilder("Transactions")
.addColumn(new ColumnBuilder("TransactionID", DataType.LONG).setAutoNumber(true))
.addColumn(new ColumnBuilder("ControllerID", DataType.LONG).setAutoNumber(false))
.addColumn(new ColumnBuilder("ReaderID", DataType.LONG).setAutoNumber(false))
.addColumn(new ColumnBuilder("Event", DataType.LONG).setAutoNumber(false))
.addColumn(new ColumnBuilder("Timestamp", DataType.SHORT_DATE_TIME).setAutoNumber(false))
.addColumn(new ColumnBuilder("Number", DataType.LONG).setAutoNumber(false))
.addIndex(new IndexBuilder(IndexBuilder.PRIMARY_KEY_NAME).addColumns("TransactionID").setPrimaryKey())
.toTable(db);
// Add the row
Map<String, Object> values = new HashMap<>();
values.put("ControllerID", cid);
values.put("ReaderID", rid);
values.put("Event", evtNum);
values.put("Timestamp", ts); // Long; must be converted to DataType.SHORT_DATE_TIME
values.put("Number", accNum);
tbl.addRowFromMap(values);
} catch (IOException IOEx) {
System.err.println(
"Failed to write record to Transactions table in database: "
+ IOEx.getMessage()
);
IOEx.printStackTrace(System.err);
} catch (Exception ex) {
System.err.println(
'[' + ex.getClass().getSimpleName() + "]: Failed to write record to "
+ "Transactions table in database: " + ex.getMessage()
);
ex.printStackTrace(System.err);
}
Executing the above code results in the following output:
DB Path to use for URL: smb://machine.vpnName/Storage/me/dbs/DBName.mdb
Failed to write record to Transactions table in database: Logon failure: account currently disabled.
jcifs.smb.SmbAuthException: Logon failure: account currently disabled.
at jcifs.smb.SmbTransport.checkStatus(SmbTransport.java:546)
at jcifs.smb.SmbTransport.send(SmbTransport.java:663)
at jcifs.smb.SmbSession.sessionSetup(SmbSession.java:390)
at jcifs.smb.SmbSession.send(SmbSession.java:218)
at jcifs.smb.SmbTree.treeConnect(SmbTree.java:176)
at jcifs.smb.SmbFile.doConnect(SmbFile.java:911)
at jcifs.smb.SmbFile.connect(SmbFile.java:957)
at jcifs.smb.SmbFile.connect0(SmbFile.java:880)
at jcifs.smb.SmbFile.open0(SmbFile.java:975)
at jcifs.smb.SmbFile.open(SmbFile.java:1009)
at jcifs.smb.SmbRandomAccessFile.<init>(SmbRandomAccessFile.java:57)
at jcifs.smb.SmbRandomAccessFile.<init>(SmbRandomAccessFile.java:42)
at samba.SmbFileChannel.<init>(SmbFileChannel.java:30)
at samba.SambaLanWriteTest.writeTest(SambaLanWriteTest.java:130)
at samba.SambaLanWriteTest.main(SambaLanWriteTest.java:181)
I have write access to a test copy of the database file in question when using Windows File Explorer. I am choosing that one when prompted.
Update 2: I realised that I neglected to add my username and password to the smb:// URL, as Thompson's example shows. I changed to code to this:
String smbCred = "smb://" + auth.getUsername() + ":" + auth.getPassword() + "@",
fixer = chosenDir.replace("\\", "/").replace("smb://", smbCred),
smbURL = fixer + dbName;
System.out.println("DB Path to use for URL: " + smbURL);
// URL u = new URL(smbURL);
The next problem I had was that my password contains special illegal characters (such as '@', ':', ';', '=' and '?'). I escaped these by using java.net.URLEncoder.encode() on auth.getUsername() and auth.getPassword() so the code doesn't throw a MalformedURLException when creating the SmbChannel. However, the next exception I encountered is as follows:
Failed to write record to Transactions table in database: File format GENERIC_JET4 [VERSION_4] does not support file creation for null
java.io.IOException: File format GENERIC_JET4 [VERSION_4] does not support file creation for null
at com.healthmarketscience.jackcess.impl.DatabaseImpl.create(DatabaseImpl.java:444)
What Database.FileFormat constant should I use for an MS Access 2000-2003 database, when creating the Database object?
It turns out that I needed to use Database.FileFormat.V2000.
After that, it was all plain sailing (although I still need to work out how to get the Long timestamp to convert correctly).