Parsing JSON feed automatically into MS Access

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 = NothingEnd Function

Json file handling in MS Access is easy. Just rename the .json extension to .txt and use the text import function with the delimiter set to (:) and the text delimiter to (").One line of code... Happy coding!