Now we found the ucanaccess project on source forge, an open source Java JDBC Driver implementation to read/write Microsoft Access database. That is a great piece of software in our case. This library uses the Jackcess library for the file IO. And there is an extension called Jackcess Encrypt to handle encrypted Access databases.
We do have a few MBS Plugins here and one is our Java plugin. For that plugin we have classes to use JDBC database connections in Xojo directly. Putting pieces together we can load the ucanaccess JDBC driver in Xojo and connect to the encrypted Microsoft Access database from client. We are happy and the application can query/write data from time to time to keep old and new system in sync.
Now if you like to see how it works, well you need all the jar (java archive) files with the required classes in a folder. This way the application can find the jar files and pass to the virtual machine on creation. Once the VM is initialized, we can create a database object, do a query and show the results:
dim appFolder as FolderItem = GetFolderItem("") // find all jar files in a java folder Dim count As Integer = appFolder.Parent.Child("java").Count dim libjs() as string For i As Integer = 1 to count Dim f As FolderItem = appFolder.Parent.Child("java").item(i) If f <> Nil and f.name.Right(4) = ".jar" Then libjs.append f.NativePath+";" End If Next // init virtual machine dim librery as string = Join(libjs, "") dim vm as new JavaVMMBS(librery) if vm.Handle = 0 then MsgBox "Failed to initialized JavaVM." else // new database connection using specific database driver class dim d as new JavaDatabaseMBS(vm,"net.ucanaccess.jdbc.UcanaccessDriver") // the file Dim DbFile as FolderItem = appFolder.Parent.Child("Database.accdb") // connect dim j as JavaConnectionMBS = d.getConnection("jdbc:ucanaccess://"+DbFile.NativePath, "", "") // run a query dim r as JavaResultSetMBS = j.MySelectSQL("Select * From test") while r.NextRecord MsgBox r.getString("FirstName") +" "+ r.getString("LastName") wend end if // any problem? Exception e as JavaExceptionMBS MsgBox e.message+" errorcode: "+str(e.ErrorNumber)