Solved Saving Received Json Data in one MS Access table

[nector]

First my apologies people one team member helped on the most complex saving Json in access table, I thought the one below was simpler, and so I was going to manage it without problems, but it is simply saving air only in the table.

Where do I go wrong here?

Received Json from cloud server which I need to save in my MS Access table.

{
   "resultCd": "000",
   "resultMsg": "It is succeeded",
   "resultDt": "20240217165848",
   "data": {
      "itemList": [
         {
            "taskCd": "7968210",
            "dclDe": "20231122",
            "itemSeq": 65,
            "dclNo": "C 82282-2023-KZU",
            "hsCd": "73262090",
            "itemNm": "POT RACK - 500MM PER MTR GALVANISE",
            "imptItemsttsCd": "2",
            "orgnNatCd": "ZA",
            "exptNatCd": "ZA",
            "pkg": 1,
            "pkgUnitCd": "PK",
            "qty": 0.02,
            "qtyUnitCd": "GRO",
            "totWt": 2,
            "netWt": 2,
            "spplrNm": null,
            "agntNm": "ESSE CLEARING LIMITED",
            "invcFcurAmt": 3323.85,
            "invcFcurCd": "ZAR",
            "invcFcurExcrt": 1.26
         },
         {
            "taskCd": "7968210",
            "dclDe": "20231122",
            "itemSeq": 64,
            "dclNo": "C 82282-2023-KZU",
            "hsCd": "73262090",
            "itemNm": "POT RACK - 500MM PER MTR GALVANISE",
            "imptItemsttsCd": "2",
            "orgnNatCd": "ZA",
            "exptNatCd": "ZA",
            "pkg": 1,
            "pkgUnitCd": "PK",
            "qty": 0.02,
            "qtyUnitCd": "GRO",
            "totWt": 2,
            "netWt": 2,
            "spplrNm": null,
            "agntNm": "ESSE CLEARING LIMITED",
            "invcFcurAmt": 3323.85,
            "invcFcurCd": "ZAR",
            "invcFcurExcrt": 1.26
         }
      ]
   }
}

VBA code being used to grab the above fields.

Private Sub CmdImports_Click()
Dim n As Integer
Dim Request As Object
Dim strData As String
Dim stUrl As String
Dim Response As String
Dim requestBody As String
Dim Company As New Dictionary
Set Company = New Dictionary

stUrl = "http://localhost:8080/sandboxty/imports/selectImportItems"

Set Request = CreateObject("MSXML2.XMLHTTP")
Company.Add "tpin", "1002623668"
Company.Add "bhfId", "000"
Company.Add "lastReqDt", Format((Me.txtImportDate), "YYYYMMDD000000")
strData = JsonConverter.ConvertToJson(Company, Whitespace:=3)
requestBody = strData
    With Request
        .Open "POST", stUrl, False
        .setRequestHeader "Content-type", "application/json"
        .send requestBody
        Response = .responseText
    End With
If Request.Status = 200 Then
MsgBox Request.responseText, vbCritical, "Internal Audit Manager"

Dim http As Object, JSON As Object, i As Integer
Dim item As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb

Set rs = db.OpenRecordset("tblImportDumpdetails", dbOpenDynaset, dbSeeChanges)
Set JSON = ParseJson(Request.responseText)
  
        rs.AddNew
        rs("taskCd") = JSON("taskCd")
        rs("dclDe") = JSON("dclDe")
        rs("itemSeq") = JSON("itemSeq")
        rs("dclNo") = JSON("dclNo")
        rs("hsCd") = JSON("hsCd")
        rs("itemNm") = JSON("itemNm")
        rs("imptItemsttsCd") = JSON("imptItemsttsCd")
        rs("orgnNatCd") = JSON("orgnNatCd")
        rs("exptNatCd") = JSON("exptNatCd")
        rs("pkg") = JSON("pkg")
        rs("pkgUnitCd") = JSON("pkgUnitCd")
        rs("qty") = JSON("qty")
        rs("qtyUnitCd") = JSON("qtyUnitCd")
        rs("totWt") = JSON("totWt")
        rs("netWt") = JSON("netWt")
        rs("spplrNm") = JSON("spplrNm")
        rs("agntNm") = JSON("agntNm")
        rs("invcFcurAmt") = JSON("invcFcurAmt")
        rs("invcFcurCd") = JSON("invcFcurCd")
        rs("invcFcurExcrt") = JSON("invcFcurExcrt")
        rs.Update
  
  
   MsgBox ("completed Invoice Deatils")

    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set JSON = Nothing
    Set item = Nothing
End If
End Sub

Attached small database with the table called tblImportDumpdetails just in case the problem could be in the table

 

[Edgar_]

Use ("data")("itemList") between JSON and fields required, like:
JSON("data")("itemList")("taskCd")

Edit
Sorry didn't notice it was an array, it will also require the index for a specific item JSON("data")("itemList")(1)("taskCd")

please check the reply after this for getting all items, instead of just one.

 

[Edgar_]

Since your itemList key is an array, if you want to add all of those, you will require a for each loop, like
Dim Item As Object
For Each Item In JSON("data")("itemList")
rs("tableField") = Item("jsonField")
...

 

[nector]

Many thanks Edgar again you have come to my rescue at the time when I spent 12 hours battling with this, we are lucky to have you on this platform

Many thanks to your sir

Regards

Nector

 

[Edgar_]

Happy to help, good luck.

 

[NauticalGent]

we are lucky to have you on this platform

Yes we are...