Parsing JSON feed automatically into MS Access

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!

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