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-5.0.0
#AdditionalJar: commons-lang3-3.8.1
#AdditionalJar: commons-logging-1.2
#AdditionalJar: hsqldb-2.5.0
#AdditionalJar: jackcess-3.0.1-B4J
#IgnoreWarnings: 15
4. There are four text files inside the zip file. Add them to the project Files folder.
5. 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:

IndieDev

Member
Trying to open a MSACCESS database which is protected with a password.
Have added "#AdditionalJar: jackcess-encrypt-3.0.0".

Throws errors and cannot open the DB.
Without password protection, the DB opens fine.

Can anyone kindly help me out here?

This is what I'm using:
B4X:
sSQL.Initialize2("net.ucanaccess.jdbc.UcanaccessDriver", "jdbc:ucanaccess://" & DBFilePath & ";memory=false;openExclusive=true;ignoreCase=true;encrypt=true", "", "passpass")
 
Last edited:

IndieDev

Member
Trying to open a MSACCESS database which is protected with a password.
Have added "#AdditionalJar: jackcess-encrypt-3.0.0".

Throws errors and cannot open the DB.
Without password protection, the DB opens fine.

Can anyone kindly help me out here?

This is what I'm using:
B4X:
sSQL.Initialize2("net.ucanaccess.jdbc.UcanaccessDriver", "jdbc:ucanaccess://" & DBFilePath & ";memory=false;openExclusive=true;ignoreCase=true;encrypt=true", "", "passpass")
This is the error...

com.healthmarketscience.jackcess.impl.UnsupportedCodecException: Decoding not supported. Please choose a CodecProvider which supports reading the current database encoding.
Am using MS Access 2016 DB. (attached)
Password is "passpass"
 

Attachments

mcqueccu

Active Member
Licensed User
Hi, I was able to open your encrypted access database @IndieDev
It was from a sample I saw sometime in the forum. Am still searching for the link to send to you. But this is how i did it

Add this additional jars
B4X:
    #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

Then Add jackcessOpener to the Initialize method
B4X:
sql.Initialize2(driverClass, $"jdbc:ucanaccess://${DBFilePath};jackcessOpener=b4j.example.main$MyOpener"$,username, password) 'change path as needed
Also, Add this
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
 

IndieDev

Member
Hi mcqueccu,

It worked!
Thanks a lot for your help.:)

This is what I had to put for the additional jars.
B4X:
    #AdditionalJar: ucanaccess-5.0.0
    #AdditionalJar: hsqldb-2.5.0
    #AdditionalJar: jackcess-3.0.1-B4J
    #AdditionalJar: jackcess-encrypt-3.0.0
    #AdditionalJar: commons-logging-1.2
    #AdditionalJar: commons-lang3-3.8.1
    #AdditionalJar: bcprov-jdk15on-166
    #AdditionalJar: jaxb-api-2.3.0
    #AdditionalJar: jaxb-impl-2.1
    #AdditionalJar: activation-2.2.1
With Regards
 

BigBoss123

Member
Licensed User
Hi IndieDev

So glad you could get it to work.
I have been having the same problem but still struggle a little on the sql.Initialize2 format.
Any chance you could show exactly the format you have used to get the connection working

Thanks a lot
 

IndieDev

Member
Hi BigBoss123,

Please find my code structure below.

B4X:
#Region  Project Attributes
    #MainFormWidth: 800
    #MainFormHeight: 600
    ' SQLite driver                             
      #AdditionalJar: ucanaccess-5.0.0
    #AdditionalJar: hsqldb-2.5.0
    #AdditionalJar: jackcess-3.0.1-B4J
    #AdditionalJar: jackcess-encrypt-3.0.0
    #AdditionalJar: commons-logging-1.2
    #AdditionalJar: commons-lang3-3.8.1
    #AdditionalJar: bcprov-jdk15on-166
    #AdditionalJar: jaxb-api-2.3.0
    #AdditionalJar: jaxb-impl-2.1
    #AdditionalJar: activation-2.2.1
    #IgnoreWarnings: 15
#End Region
Variables declared in Process_Globals:
B4X:
    Private gSQL As SQL
    Private DBFile As String
    Dim sSQL As String
    Dim sDBpass As String
Function to Initialize the DB:
B4X:
Sub Database_Init As Boolean
    Dim Result As Boolean = False
    
    DBFile = File.DirData("PKDBServer") & "\" & "users.accdb"
    sDBpass = "passpass"
    sSQL = "jdbc:ucanaccess://" & DBFile & ";jackcessOpener=b4j.PKDBServer.main$MyOpener;memory=false;ignorecase=true;encrypt=true;COLUMNORDER=DISPLAY;singleconnection=true"
    
    Try
        Log(" Init sql connection...")
        gSQL.Initialize2("net.ucanaccess.jdbc.UcanaccessDriver", sSQL,"", sDBpass)
        Result = True
    Catch
        Log(LastException.Message)
    End Try
    Return Result
End Sub
JAVA code at the end of the MAIN page:
B4X:
#If Java
    import java.io.File;
    import java.io.IOException;
    import net.ucanaccess.jdbc.JackcessOpenerInterface;
    import com.healthmarketscience.jackcess.CryptCodecProvider;
    import com.healthmarketscience.jackcess.Database;
    import com.healthmarketscience.jackcess.DatabaseBuilder;
    import java.sql.SQLException;

    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
 
Top