B4J Code Snippet Opening MS Access databases (mdb)

Discussion in 'B4J Code Snippets' started by Erel, Jul 24, 2019.

  1. Erel

    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:
    #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:
    '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: Jul 24, 2019
  2. Erel

    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:
    #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:
    #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:
    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
  3. universengo

    universengo Member

    I have followed your instructions and succeeded!
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice