WKL Office JSON: JSON in Access and VBA

VBA has no JSON parser of its own, and a number read as Double keeps only 15 to 17 significant digits: 9007199254740993 comes back as 9007199254740992. WKL Office JSON gives Access and VBA the object model you know from MSXML. You load a web service response, query it with JPath, read typed values, change or create nodes, and write JSON text or a file; amounts and 19-digit identifiers are read without a detour through Double. There is one DLL per Office bitness and no .NET runtime to install. What the library accepts, converts and refuses: Values and limits.

Up to release 1.0.3 the library was called WKL Access JSON.

graph TD
    D["JDoc – LoadJson, Load, Save, Json, ParseError, DateZone"] -->|"Root"| N["JNode (any node) – Item, Value, Path, ChildNodes, JPath, SelectNodes, GetString …"]
    N --> E["JElement – JSON object: Keys, HasKey, SetValue, AddElement, AddArray, RemoveKey"]
    N --> A["JArray – JSON array: Add, Insert, RemoveAt"]
    N --> V["JValue – string, number, true/false, null"]
    N -->|"SelectNodes, ChildNodes"| L["JNodeList – Item, Count, For Each"]
    D -->|"failed load"| P["JParseError – Line, LinePos, SrcText, Reason"]

Key features

MSXML concepts: JDoc as document, LoadJson with ParseError, SelectSingleNode and SelectNodes, node lists with For Each.
JPath after RFC 9535 (JSONPath): paths, wildcards, recursive descent, slices, filters with comparisons and functions.
Typed getters with defaults: GetString, GetLong, GetDouble, GetCurrency, GetDecimal, GetDate, GetBoolean, GetValue, each with an optional default like Nz().
Exact numbers: numbers keep the text they were read with, so Json, Save and GetString never change a digit. GetCurrency and GetDecimal convert that text without a detour through Double; Currency then rounds to four decimal places, Decimal to 28-29 significant digits. 19-digit identifiers survive as Decimal.
Dates: ISO 8601 (RFC 3339 profile) in both directions, with a document-wide rule for time zones.
Building JSON: SetValue creates missing objects and arrays along the path; nested objects, copies of nodes and removal of nodes.
Access-like behavior: default member Item, 0-based positions, Is for identity, fixed error numbers with the path or position in the description.
No runtime installation: a NativeAOT DLL per bitness.

Core concepts

Document and nodes. A JDoc holds one tree. Every node is a JNode; its concrete class is JElement (object), JArray or JValue, and NodeType tells which. Nodes created with CreateElement, CreateArray and CreateValue are free until they are inserted.

Paths. JPath expressions start at the root ($) or, without $, at the node they are called on. SelectSingleNode returns the first match or Nothing, SelectNodes all matches, Exists tells whether there is one; the getters read the first match as a typed value.

Values. Reading a number returns the natural VBA type (Long, Decimal or Double); the getters convert to the requested type and refuse values that do not fit instead of truncating them. JSON null is VBA Null.

Errors. Loading returns False and fills ParseError, like MSXML. All other failures raise a VBA error with a JErrorCode number; a failure the library does not anticipate arrives as &H8000FFFF with the text of the cause, out of memory as &H8007000E. The messages (ParseError.Reason, Err.Description) are English, German, Slovenian, Croatian or Italian: the language the WKL controls use in the same process, otherwise the user interface language of Windows, otherwise English. Decide by the number, never by the text; the number is the same in every language.

WKL Office JSON: JSON in Access and VBA

Key features

Core concepts

Related topics