Microsoft Access development tools
Color scheme: Dark
Riviera
Light
Dark

Office JSON · for Access and VBA developers

A JSON parser for Access and VBA, with the object model of MSXML

Load web service responses, query them with JPath, read typed values. Amounts and IDs arrive exactly as they were sent.

JDoc, LoadJson with ParseError, SelectNodes, and For Each work the way they do with XML. On users' PCs, all it takes is one file per Office bitness—no .NET, no setup, no admin rights.

Tutorial: read and write JSON in VBA Where JSON in VBA gets stuck today
  1. Web service response
  2. JDoc and JPath
  3. Typed value

LoadJson reads the response, a JPath expression finds the field, and a getter returns it as Currency, Decimal, Date, or String.

The situation

VBA reads XML with MSXML. For JSON, there's nothing like it.

Web services, payment providers, and government APIs answer in JSON these days. In Access and VBA, that usually means picking the response apart with string functions, using ScriptControl, or pulling in a VBA module from the internet.

  • Amounts and long IDs lose digits. Read a number as a Double and you keep 15 to 17 significant digits: a 19-digit ID comes back changed, and 0.1 + 0.2 isn't exactly 0.3.
  • Dates are just text. JSON has no date type. Convert time stamps with time zones yourself, and appointments end up a few hours off.
  • Loops instead of queries. A field deep in the document means working your way down level by level and checking every spot where something could be missing.

Why not VBA-JSON?

For plenty of projects, it's enough—until exact amounts matter

Most forums point to VBA-JSON, an MIT-licensed module: no installation, the source is right there, and it handles simple responses just fine. By default, it even returns long numbers as text, so IDs keep their digits. Every other number, though, becomes a Double—amounts like 0.1 included. ScriptControl, the other common route, is only available in 32-bit Office.

Office JSON keeps the text of every number and converts it only when you ask for a type—without going through Double. If a value doesn't fit that type, the getter raises an error instead of truncating it. Values and limits: type mapping, numbers, dates

With Office JSON

Read JSON the way you read XML

Request a customer from a REST service and read the name. If the text isn't JSON, LoadJson returns False and describes the problem in ParseError—just like loadXML in MSXML.

The example is taken unchanged from the how-to guide Call a web service and read its JSON response.

Public Function GetCustomerName(ByVal customerId As Long) As String    Dim http As Object    Dim doc As New JDoc     Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0")    http.Open "GET", "https://api.example.com/customers/" & customerId, False    http.setRequestHeader "Accept", "application/json"    http.send     If http.Status <> 200 Then        Err.Raise vbObjectError + 1000, "GetCustomerName", "HTTP " & http.Status & ": " & http.statusText    End If     If Not doc.LoadJson(http.responseText) Then        Err.Raise vbObjectError + 1001, "GetCustomerName", _            "Invalid JSON in line " & doc.ParseError.Line & ": " & doc.ParseError.Reason    End If     GetCustomerName = doc.GetString("$.name", "")End Function
  • Nothing new to learn. JDoc as the document, LoadJson with ParseError, SelectSingleNode, SelectNodes, and node lists with For Each—the concepts of MSXML.
  • Exact numbers. Json, Save, and GetString never change a digit. GetCurrency and GetDecimal convert without going through Double: 0.1 + 0.2 stays exact, and 19-digit IDs survive as Decimal. Currency rounds to four decimal places, Decimal to 28 or 29 significant digits.
  • Queries instead of loops. JPath per RFC 9535: paths, wildcards, recursive descent, slices, and filters.
  • Typed values with a default. GetString, GetLong, GetCurrency, GetDecimal, GetDate, and more, each with an optional default like Nz().
  • ISO 8601 dates in both directions, with one time zone rule for the whole document.
  • Handle errors by number. Fixed error numbers with the path or position in the description; the number is the same in every language.

Status and limits

Is Office JSON a fit for your application?

Released
It's a fit if your application runs in Microsoft Access from Microsoft 365 on Windows 11—32-bit or 64-bit, as an ACCDB, an ACCDE, or with the Access Runtime. Check these points first:

  • Access 2016, 2019, 2021, or LTSC, other Windows versions: not supported in this release.
  • The file matches the bitness of Office, not Windows. It's registered per Windows user without admin rights—when you ship your application with Bootstrapper, that happens on delivery.
  • Strict RFC 8259: comments, trailing commas, and single quotes are errors with line and column—even where other tools accept that kind of text.
  • Formerly WKL Access JSON: a reference to the old type library resolves without recompiling; library ID, ProgIDs, and members are unchanged.

Install and deploy Office JSON

Start here

Want to know what Office JSON includes and how it gets to your users? The product page covers features, requirements, and limits.

See the product page

Prefer to talk first? We'll look at your interface with you · All tools for Microsoft Access · Product overview