B4J Code Snippet Opening MS Access databases (mdb)

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:

#AdditionalJar: ucanaccess-5.0.0
#AdditionalJar: commons-lang3-3.8.1
#AdditionalJar: commons-logging-1.2
#AdditionalJar: hsqldb-2.6.1
#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:

'SQL object from jSQL library
sql.Initialize("net.ucanaccess.jdbc.UcanaccessDriver", "jdbc:ucanaccess://C:/Users/H/Downloads/1.accdb") 'change path as needed

 

[Erel]

Instructions were updated based on a new version of the libraries that will work with B4JPackager11.

Note that you need to add several files to the Files folder.

 

[Erel]

Note that creating a new database will fail when the app is packaged. It will work during development. The solution is simple, put an empty database in the Files folder and copy it to the target path if the database file doesn't exist.

 

[IndieDev]

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:

sSQL.Initialize2("net.ucanaccess.jdbc.UcanaccessDriver", "jdbc:ucanaccess://" & DBFilePath & ";memory=false;openExclusive=true;ignoreCase=true;encrypt=true", "", "passpass")

 

[IndieDev]

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:

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"

 

[Erel]

Based on the error, the library doesn't support the encryption method.

 

[IndieDev]

Thank you.

That means I'll have to look for another way of using jackcess-encrypt ..... ?

 

[mcqueccu]

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

    #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

sql.Initialize2(driverClass, $"jdbc:ucanaccess://${DBFilePath};jackcessOpener=b4j.example.main$MyOpener"$,username, password) 'change path as needed

Also, Add this

#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]

Hi mcqueccu,

It worked!
Thanks a lot for your help.

This is what I had to put for the additional jars.

    #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]

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

 

[Jorge M A]

Any chance you could show exactly the format you have used to get the connection working

Sample project attached.
See the Files tab. You need to unzip the txt files inside.

 

[BigBoss123]

Thanks ever so much

 

[IndieDev]

Hi BigBoss123,

Please find my code structure below.

#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:

    Private gSQL As SQL
    Private DBFile As String
    Dim sSQL As String
    Dim sDBpass As String

Function to Initialize the DB:

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:

#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

 

[Wendell Carneiro Mendes]

Good morning friends
I would like to know if there is such a code for B4A?
Thankful...

 

[Erel]

1. There is an Android library named Jackcess.
2. This solution might also work. You need to implement it using JdbcSQL instead of SQL.

 

[OliverA]

I would like to know if there is such a code for B4A?

Here's a demo for accessing .mdb's on Android: https://www.b4x.com/android/forum/t...emo-works-under-b4j-see-notes.121546/#content
If you're looking for accessing encrypted .mdb's on Android, then the above code may work on Android. What you need to watch out for is the library versions that are compatible with Android. For example, UCanAccess is currently at version 5.0.0. This version will not work under Android, you'll have to use 4.0.4.

 

[Wendell Carneiro Mendes]

Thank you,
I'll check, even so I appreciate the attention.

 

[Koong]

Hi, Can someone to help me to send some example for ms-access to b4a ?

 

[Erel]

Just use JdbcSQL instead of SQL.

 

[OliverA]

Hi, Can someone to help me to send some example for ms-access to b4a ?

See https://www.b4x.com/android/forum/threads/b4a-ucanaccess-demo-works-under-b4j-see-notes.121546/