Share My Creation [BANanoVuetifyAD3] BANanoServer - Direct MS-ACCESS CRUD

Hi there

A rather incredible opportunity!!! I mean accessing MS-ACCESS databases directly from the jetty server with jSQL.

Download

This has been rather a tricky exercise.

1st, thanks to @Erel post here. BTW, you need to follow the steps on that post first to make this work.

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...

www.b4x.com

2nd @OliverA , pointed us to to solve the CRUD issue, here.

Jrdc with ucanaccess not committing inserts (Solved)

I have Jrdc connecting with ms access ( would prefer not to use access but stuck with it for now). Its working fine for select queries but having trouble with inserts. I am testing on a simple table with an auto 'id' column and 'UserName' text column with unique constraint. I have the config...

www.b4x.com

Which originally was found by @jmon back in 2017.

JSQL and UcanAccess

Good day! I am working with Access and B4J via UcanAccess, so far so good, everything works with the SQL class provided in JSQL library, the only thing i cannot make it work is TransactionSuccessful method, it is funny because the BeginTransaction actually is doing the job, but because even...

www.b4x.com

We have set up the DataSource to point to the location of our db.

Then in SERVERIndex...

Dim dDB As DirectDB
    dDB.Initialize
    If dDB.OpenMSAccess(File.DirApp, dbname) Then
        Select Case scommand
        Case "insert", "update", "delete", "createtable"
            dDB.SQLRunQuery(query, args)
            payload2browser.Put("error", dDB.error)
            payload2browser.Put("ok", dDB.ok)
            payload2browser.Put("result", returnList)
        Case Else
            returnList = dDB.SQLExecuteMaps(query, args)
            payload2browser.Put("error", dDB.error)
            payload2browser.Put("ok", dDB.ok)
            payload2browser.Put("result", returnList)
        End Select
        dDB.SQLClose
    Else
        payload2browser.Put("error", "Cannot connect to the database!")
        payload2browser.Put("ok", False)
        payload2browser.Put("result", returnList)
    End If   
    ws.RunFunction("directmsaccessonbrowser_result", Array(payload2browser))
    ws.Flush    ...

The OpenMSAccess commands include...

    Dim dbPath As String = File.Combine(Dir, fileName)
        dbPath = dbPath.Replace("\","/")
        aSQL.Initialize("net.ucanaccess.jdbc.UcanaccessDriver", $"jdbc:ucanaccess://${dbPath}"$)

This should be available soon: