Get data from Json format into MS access

How to get live data for a point of sales machine with data in Json format into Ms Access tables ,like below:

(1) tblCustomerInvoice (Primary table)
(2) SalesDetailline (Child table)

Parameters and data type are as below:

Json: char *TaxCode, char *Productcode, char *number, char *date, char *CustomerID, char * amount, char *SpecialCode, char* intKey

Access: text TaxCode, Text Productcode, number Number,date Date, Int CustomerId , double Double , text spcialcode , autonumber Primary key

The only sure way is to use VBA , any idea on how to do the following:

(1) VBA function to get the data and insert into the tblcustomerinvoice and tblsaledetailsline

What we want to archive here is when a person is punching data on a POS machine , then simultaneously the same data must be entered in the above two ( tblcustomerinvoice and tblsaledetailsline) this means that we won't need to recapture the same data again.

Regards

Chris

John Tsioumpris

For start you need a json parser...a function that will turn the json data to ready to insert values.
This seems like a good place to start.
Probably ...just probably ...your POS holds a normal OS (e.g Windows/Linux) and an own database engine...so you could link directly to the POS and get the data "streamed" to your own application,,,

Norie

Chris

I've used this for JSON data when VBA was all I had available, VBA-JSON.

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

ASKER

Okay I have seen the Jsonconverter.bas I have put in module for testing it has compiled without errors , now I thought there should be another code where I'm supposed to put parameters like below:

Json: char *TaxCode, char *Productcode, char *number, char *date, char *CustomerID, char * amount, char *SpecialCode, char* intKey

Access: text TaxCode, Text Productcode, number Number,date Date, Int CustomerId , double Double , text spcialcode , autonumber Primary key

Regards

Chris

John Tsioumpris

@Norie i already posted the Github link

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

ASKER

What i'm asking is after Jsonconverter.bas which code should select to allow me to put the parameters??????????

Regards

Chris

SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Norie

John

Sorry, I didn't see any link in your post.:)

SOLUTION

John Tsioumpris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Tsioumpris

@Norie...next time i will make it bigger and better... :)

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

ASKER

Correct me if the code below is not required, even if it failing compile , it gives an indicator that the parameters are required to feed the sql:

[code] Private Function JSONImport()
    Dim db As Database, qdef As QueryDef
    Dim FileNum As Integer
    Dim DataLine As String, jsonStr As String, strSQL As String
    Dim p As Object, element As Variant

    Set db = CurrentDb

    ' READ FROM EXTERNAL FILE
    FileNum = FreeFile()
    Open "C:\Path\To\JsonFile.json" For Input As #FileNum

    ' PARSE FILE STRING
    jsonStr = ""
    While Not EOF(FileNum)
        Line Input #FileNum, DataLine

        jsonStr = jsonStr & DataLine & vbNewLine
    Wend
    Close #FileNum
    Set p = ParseJson

    ' ITERATE THROUGH DATA ROWS, APPENDING TO TABLE
    For Each element In p
        strSQL = "PARAMETERS [col1] Long, [col2] Text(255), [col3] Text(255), " _
                          & "[col4] Text(255), [col5] Text(255); " _
                  & "INSERT INTO TableName (col1, col2, col3, col4, col5) " _
                          & "VALUES([col1], [col2], [col3], [col4], [col5]);"

        Set qdef = db.CreateQueryDef("", strSQL)

        qdef!col1 = element("col1")
        qdef!col2 = element("col2")
        qdef!col3 = element("col3")
        qdef!col4 = element("col4")
        qdef!col5 = element("col5")

        qdef.Execute
    Next element

    Set element = Nothing
    Set p = Nothing
End Function

Open in new window

[/code]

See also error

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

ASKER

Thank you Norie , you have sorted out my problem!

Set p = JsonConverter.ParseJson(jsonStr)

Regards

Chris

John Tsioumpris

@Jim thanks...good to know