Expert guide

Parse JSON in Microsoft Access VBA

Read API responses into tables reliably, including nested objects, arrays and nulls

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: import the open-source VBA-JSON module (JsonConverter.bas), add a reference to Microsoft Scripting Runtime, then use Set json = JsonConverter.ParseJson(responseText). Objects become Dictionaries (json("key")), arrays become 1-based Collections (json("items")(1)), and null becomes VBA Null. Loop with For Each and write values to a DAO recordset. Avoid ScriptControl-based parsing; it does not work on 64-bit Office. For full integrations, see API integration with Access and Excel.

Almost every modern system speaks JSON: payment providers, shipping carriers, CRMs, e-commerce platforms, accounting software and AI models. Access has no built-in JSON support, which is why integration projects often stall at the step after "the API call worked". Parsing JSON correctly, including the awkward parts, takes a good parser and a few patterns.

Step 1: Set Up VBA-JSON

  • Download JsonConverter.bas from the VBA-JSON project on GitHub (MIT licence).

  • In the VBA editor: File → Import File → JsonConverter.bas.

  • Tools → References → tick Microsoft Scripting Runtime.

  • Compile (Debug → Compile) to confirm everything resolves.

VBA-JSON is pure VBA, so it works in 32-bit and 64-bit Office and in the Access Runtime. It has been used in production integrations for years.

How JSON Maps to VBA

JSONBecomes in VBAAccess it with
{ object }Scripting.Dictionaryobj("fieldName")
[ array ]Collection (1-based)arr(1), For Each item In arr
"string"StringDirect
numberDouble (or Long / Decimal)Direct; CCur for money
true / falseBooleanDirect
nullNullIsNull(), Nz()

Step 2: Read a Simple Response

' Response: {"id": 4471, "status": "shipped", "carrier": {"name": "UPS",
'            "tracking": "1Z999AA10123456784"}, "delivered_at": null}
Dim json As Object
Set json = JsonConverter.ParseJson(http.responseText)

Debug.Print json("id")                        ' 4471
Debug.Print json("status")                    ' shipped
Debug.Print json("carrier")("tracking")       ' 1Z999AA10123456784
Debug.Print Nz(json("delivered_at"), "not yet")

Step 3: Loop Over an Array Into a Table

' Response: {"orders": [ {"number": "SO-1001", "total": 249.5,
'   "created": "2026-11-03T14:22:05Z", "lines": [ {...}, {...} ]}, ... ]}
Public Sub ImportOrders(ByVal responseText As String)
    Dim json As Object, o As Object, ln As Object
    Dim db As DAO.Database, rsO As DAO.Recordset, rsL As DAO.Recordset
    Dim newID As Long

    Set json = JsonConverter.ParseJson(responseText)
    Set db = CurrentDb
    Set rsO = db.OpenRecordset("tblWebOrders", dbOpenDynaset)
    Set rsL = db.OpenRecordset("tblWebOrderLines", dbOpenDynaset)

    For Each o In json("orders")
        If DCount("*", "tblWebOrders", "OrderNo='" & o("number") & "'") = 0 Then
            rsO.AddNew
            rsO!OrderNo = o("number")
            rsO!Total = CCur(o("total"))
            rsO!CreatedOn = IsoToDate(o("created"))
            If o.Exists("note") Then rsO!Note = Nz(o("note"), Null)
            rsO.Update
            rsO.Bookmark = rsO.LastModified
            newID = rsO!WebOrderID

            For Each ln In o("lines")
                rsL.AddNew
                rsL!WebOrderID = newID
                rsL!SKU = ln("sku")
                rsL!Qty = ln("quantity")
                rsL!UnitPrice = CCur(ln("price"))
                rsL.Update
            Next ln
        End If
    Next o
    rsO.Close: rsL.Close
End Sub

' "2026-11-03T14:22:05Z" -> Access Date (UTC)
Public Function IsoToDate(ByVal s As Variant) As Variant
    If IsNull(s) Or Len(s) < 10 Then IsoToDate = Null: Exit Function
    IsoToDate = DateSerial(Mid$(s, 1, 4), Mid$(s, 6, 2), Mid$(s, 9, 2))
    If Len(s) >= 19 Then IsoToDate = IsoToDate + _
        TimeSerial(Mid$(s, 12, 2), Mid$(s, 15, 2), Mid$(s, 18, 2))
End Function

Three habits in that code prevent most integration bugs: check for duplicates before inserting, use .Exists before reading optional fields, and read the new AutoNumber with Bookmark = LastModified after Update, which also works when the table is linked to SQL Server.

Step 4: Parse Structured AI Responses

When you ask an AI model to return JSON, its reply is JSON inside a JSON string. Parse twice.

Dim outer As Object, inner As Object, content As String
Set outer = JsonConverter.ParseJson(http.responseText)
content = outer("choices")(1)("message")("content")

' Models sometimes wrap JSON in markdown code fences; strip them
Dim fence As String
fence = String$(3, Chr$(96))              ' three backtick characters
content = Replace(Replace(content, fence & "json", ""), fence, "")
content = Trim$(content)

Set inner = JsonConverter.ParseJson(content)
Me.Category = inner("category")
Me.Priority = inner("priority")
Me.Summary = inner("summary")

Note the Collection index: choices(1), not choices(0). That off-by-one is the most common error when porting JavaScript or Python examples to VBA. Even better, request structured JSON output from the API so the model never adds fences. For the API call itself, see connecting MS Access to the OpenAI API.

Step 5: Build JSON to Send

Dim body As New Scripting.Dictionary, lines As New Collection, ln As Scripting.Dictionary
body("customer_id") = Me.CustomerID
body("po_number") = Nz(Me.PONumber, Null)
body("ship_date") = Format(Me.ShipDate, "yyyy-mm-dd")

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT SKU, Qty FROM tblOrderLines WHERE OrderID=" & Me.OrderID)
Do While Not rs.EOF
    Set ln = New Scripting.Dictionary
    ln("sku") = rs!SKU: ln("quantity") = rs!Qty
    lines.Add ln
    rs.MoveNext
Loop
body("lines") = lines

http.send JsonConverter.ConvertToJson(body)

Errors You Will Meet

ErrorCauseFix
Error parsing JSON: unexpected characterResponse is HTML (an error page) or wrapped text, not JSONCheck http.Status and print the first 200 characters
Run-time 5: Invalid procedure callReading a key that does not existUse .Exists first
Run-time 9: Subscript out of rangeIndex 0 on a CollectionCollections start at 1
Run-time 94: Invalid use of NullNull assigned to a String or used in arithmeticNz() or IsNull() checks
Run-time 424: Object requiredUsing Set on a plain value, or not using Set on an objectSet for Dictionaries and Collections only

From Script to Integration

Parsing is one piece. A dependable integration also needs authentication handling, paging through results, retries, logging, and a clear rule for what happens when the API and the database disagree. That is the work in our Access and Excel API integration projects, including QuickBooks, shipping carriers, e-commerce platforms and AI services.

FAQ

JSON in Access VBA FAQ

Parsing, nulls, arrays and sending data

The most reliable approach is the open-source VBA-JSON module (JsonConverter.bas). Import it, add a reference to Microsoft Scripting Runtime, and call JsonConverter.ParseJson on the response text. Objects become Scripting.Dictionary items and arrays become Collections, which you navigate with keys and 1-based indexes.

The old trick of using MSScriptControl.ScriptControl to evaluate JSON as JavaScript only works in 32-bit Office, because the ScriptControl component has no 64-bit version. It is also a security concern because it executes the text. Use a pure-VBA parser instead.

After ParseJson, an array is a Collection. Use For Each item In json("items") to loop, where each item is usually a Dictionary. Read fields with item("name"). Check item.Exists("field") before reading optional fields to avoid errors.

VBA-JSON returns JSON null as VBA Null. Writing Null to an Access field is fine if the field allows it; to put it in a String variable, wrap it with Nz(value, ""). Use IsNull() to test before converting numbers or dates.

Build a Dictionary (and Collections for arrays), fill it from your record, and call JsonConverter.ConvertToJson. This handles quoting and escaping correctly, which is safer than concatenating strings by hand.