B4J Code Snippet Opening MS Access databases (mdb)

Erel

Administrator
Staff member
Licensed User
1. Download UCanAccess: https://www.b4x.com/b4j/files/ucanaccess.zip
(Sources: https://jackcessencrypt.sourceforge.io/ and http://ucanaccess.sourceforge.net/site.html)
The libraries are licensed with Apache 2.0 license: http://www.apache.org/licenses/LICENSE-2.0

2. Copy the jars to the additional libs folder.
3. Add:
B4X:
#AdditionalJar: ucanaccess-4.0.4
#AdditionalJar: jackcess-2.1.11
#AdditionalJar: hsqldb
#AdditionalJar: commons-logging-1.1.3
#AdditionalJar: commons-lang-2.6
4. Open the database:
B4X:
'SQL object from jSQL library
sql.Initialize("net.ucanaccess.jdbc.UcanaccessDriver", "jdbc:ucanaccess://C:/Users/H/Downloads/1.accdb") 'change path as needed
 
Last edited:

Erel

Administrator
Staff member
Licensed User
If you try to open an encrypted database you will get an error message:
net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::4.0.4 Decoding not supported. Please choose a CodecProvider which supports reading the current database encoding.

Opening encrypted databases instructions:
1.
B4X:
#AdditionalJar: ucanaccess-4.0.4
#AdditionalJar: jackcess-2.1.11
#AdditionalJar: hsqldb
#AdditionalJar: commons-logging-1.1.3
#AdditionalJar: commons-lang-2.6
#AdditionalJar: commons-lang3-3.0
#AdditionalJar: jackcess-encrypt-2.1.4
#AdditionalJar: bcprov-jdk15on-150
#AdditionalJar: jaxb-api-2.4.0.jar
#AdditionalJar: jaxb-impl-2.1.jar
#AdditionalJar: activation-1.0.2.jar
2. Inline java code:
B4X:
#if JAVA
import java.io.File;
import java.io.IOException;
import com.healthmarketscience.jackcess.*;
import com.healthmarketscience.jackcess.CryptCodecProvider;
public static class MyOpener implements net.ucanaccess.jdbc.JackcessOpenerInterface {
public Database open(File fl,String pwd) throws IOException {
   Database db = new DatabaseBuilder(fl).setCodecProvider(new CryptCodecProvider(pwd)).open();
   return db;
}
}
#End If
3. Open the database with code similar to:
B4X:
Dim OpenerClass As String = "b4j.example.main$MyOpener" 'change package name and module as needed.
sql.Initialize2("net.ucanaccess.jdbc.UcanaccessDriver", $"jdbc:ucanaccess://C:/Users/H/Downloads/2.accdb;jackcessOpener=${OpenerClass}"$, _
   "username", "password") 'change path as needed
 
Top