Encrypted Access database in Xojo
If you like to use an Microsoft Access database cross platform on Mac OS X, Windows and Linux, your options are quite limited. But some clients have a need to read data from Access database and process it in their own application.
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)
