Get data from Json format into MS access

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
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,,,
NorieAnalyst Assistant

Commented:
Chris

I've used this for JSON data when VBA was all I had available, VBA-JSON.
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

John TsioumprisSoftware & Systems Engineer

Commented:
@Norie i already posted the Github link
What i'm asking is after  Jsonconverter.bas which code should select to allow me to put the parameters??????????


Regards

Chris
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
You can use collections to decode the Json data - as described in several of my articles, for example:

Exchange Rates and Currency Conversion in VBA

for example, the function ExchangeRatesCca.

These covers also how to pick up  the Json data, as these often are provided via a web service to be retrieved online.
NorieAnalyst Assistant

Commented:
John

Sorry, I didn't see any link in your post.:)
John TsioumprisSoftware & Systems Engineer
Commented:
It seems it starts by passing the json data to Public Function ParseJson..this will parse the json string and will return an object which in turn you will iterate and create the INSERT string
John TsioumprisSoftware & Systems Engineer

Commented:
@Norie...next time i will make it bigger and better... :)
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

json.png
Analyst Assistant
Commented:
I think that this,
 Set p = ParseJson

Open in new window

should be something like this.
Set p = JsonConverter.ParseJson(jsonStr)

Open in new window

Thank you Norie , you have sorted out my problem!

Set p = JsonConverter.ParseJson(jsonStr)

Regards

Chris
John TsioumprisSoftware & Systems Engineer

Commented:
@Jim thanks...good to know

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial