Parsing JSON feed automatically into MS Access

Question

My company has a vendor providing a JSON feed of data that I need to load into our MS Access database every two hours. I need to:

1. load the data from the feed,
2. parse the JSON into a usable format for Access, and then
3. insert it into the database.

I came across this question discussing a similar issue, but there's no good description there as to how to implement this in MS Access. Any help gratefully appreciated!

Answer 1

Using the VBA JSON library, you certainly can import JSON formatted files into MS Access. The idea is to consider JSON data as a collection of dictionaries and Visual Basic provides the collection and dictionary as data structures.

Below are the steps:

1. Build a table to match the structure of expected JSON data
2. On the VBA IDE side of MS Access, import the JsonConverter.bas (from link above) into a new module
3. Still in the IDE, under Tools / References, check off the VBA Reference: Microsoft Scripting Runtime
4. Include the following code that reads the JSON text file, parses it as a collection of dictionaries (with keys and valeus), and appends values iteratively into Access table. Place code behind an Access form or module (example uses a one nested level JSON file)

JSON

[
  {
    "col1": somenumber,
    "col2": "somestring",
    "col3": "somestring",
    "col4": "somestring",
    "col5": "somestring"
  }
]

VBA 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(jsonStr)

    ' 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