MS Access Parsing Nested Arrays using VBA and JSON

Question

Have written code to call API using VBA from MS Access, but having issues extracting nested data. Am able to retrieve data from Items set, but also want to retrieve data from Attributes set where SetName=DG (in the below example there is only 1 AttributeSet, but could be more). Have followed some of the other similar posts, but just not working. Returned Data:

{"Pagination":
{"NumberOfItems":1,
"PageSize":200,
"PageNumber":1,
"NumberOfPages":1},
"Items":[
{"ProductCode":"TEST",
"ProductDescription":"TEST",
"UnitOfMeasure":
{"Guid":"7e420466-4ced-48df-bb41-1693fe34a32c",
"Name":"EA",
"Obsolete":false},
"NeverDiminishing":false,
"ImageUrl":null,
"SellPriceTier1":
{"Name":"Sell Price Tier 1",
"Value":null},
"SellPriceTier2":
{"Name":"Sell Price Tier 2",
"Value":null},
"Supplier":null,
"AttributeSet":
{"Guid":"c3bd26c9-424a-4786-adbe-7c5a98b8f422",
"SetName":"DG",
"Type":"Product",
"Attributes":[
{"Guid":"6164f12b-2cb9-491c-b932-e6fb050579df",
"Name":"UN",
"Value":"1993",
"IsRequired":false},
{"Guid":"aa13f1dd-2174-4993-b80d-22bf4f4f27da",
"Name":"Technical Name",
"Value":"2K REDUCED",
"IsRequired":false},
{"Guid":"664fbcd6-83be-4afc-b812-22c97ae38949",
"Name":"Flash Point",
"Value":"30",
"IsRequired":false},
{"Guid":"3bc41b7c-bd14-44f6-a6b0-72d1ba84adbb",
"Name":"Pack Group",
"Value":"III",
"IsRequired":false}]},
"IsSellable":true,
"AlternateUnitsOfMeasure":[
{"Guid":"d42f5682-02b3-43fa-a848-46e6023c3b9e",
"Name":"LT",
"ConversionRate":1.0000,
"ForPurchases":true}],
"LastModifiedOn":"\/Date(1674949964652)\/"}
]
}

Code:

Dim key_id, secret_key, URL As String
Dim strJson As String
Dim req As New XMLHTTP60
Dim strModule As String
Dim strFilter As String
Dim rs, rs1 As DAO.Recordset
Dim JsonText As Object
Dim Item As Object
Dim attset As Object
Dim att As Object
key_id = "API ID"
secret_key = "API key"
URL = "https://api.unleashedsoftware.com/"
strModule = "Products?"
strFilter = "productCode=TEST&includeAttributes=True"
strJson = URL + strModule + strFilter

req.Open "GET", strJson, False
req.setRequestHeader "api-auth-id", key_id
req.setRequestHeader "api-auth-signature", Base64HMAC("SHA256", strFilter, secret_key)
req.setRequestHeader "Content-Type", "application/json"
req.setRequestHeader "Accept", "application/json"
req.sEnd

Set JsonText = JsonConverter.ParseJson(req.responseText)

For Each Item In JsonText("Items")
Set rs = CurrentDb.OpenRecordset("aProduct")
With rs
    .AddNew
    !prodcode = Item("ProductCode")
    !proddesc = Item("ProdutDescription")
    .Update
End With

    For Each att In Item("Attributes")
        'get Attribute Value
        Set rs1 = CurrentDb.OpenRecordset("aProdDGAttribute")
        With rs1
            .AddNew
            !Name = att("Name")
            !Value= att("Value")
            .Update
        End With
    Next att

Next Item

MsgBox "done"

locals window screenshot:

"Not working" means what - error message, wrong result, nothing happens? — June7
– June7, Commented Jan 29, 2023 at 8:17
Items appears to be within pagination, so yeah, that wouldn't work. Inspect the result of JsonText to get a sense of how it's structured — Erik A
– Erik A, Commented Jan 29, 2023 at 9:25
@June7, any code i try for the 2nd For loop fails, usually with an object not found error, so just wanting the correct way to handle. Not an expert on this, but previous work has worked with structure of For Each att In Item("Attributes"), although in this case the attributes are another level down. — dbblmb
– dbblmb, Commented Jan 29, 2023 at 9:58
By inspecting it through the locals window. Set a break point after it's set. — Erik A
– Erik A, Commented Jan 29, 2023 at 10:09

June7 · Accepted Answer · 2023-02-01 02:12:07Z

0

Consider this which works for the given sample showing only one AttributeSet:

Dim First As Variant, Second As Variant
Dim items As Object, atts As Object

Set items = jSon("Items")
For Each First In items
    Debug.Print First("ProductCode")
    Debug.Print First("ProductDescription")
    Debug.Print First("AttributeSet")("SetName")
    Set atts = First("AttributeSet")("Attributes")
    For Each Second In atts
        Debug.Print Second("Name")
        Debug.Print Second("Value")
    Next
Next

Guidance from Parse JSON objects and collection using VBA

edited Feb 1, 2023 at 2:12

answered Jan 31, 2023 at 12:45

June7

21.7k8 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MS Access Parsing Nested Arrays using VBA and JSON

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related