Please see code below for parsing data from json into MS Access tables using VBA. The first level (Order) works fine, it's the second level (OrderLine) that is getting an error, and not quite sure how to get the OrderDetailID (which is an auto-number) from the first table into the second table to be able to link. I have been using some code i found online and replicated but something not quite right. I'm using the Tim Hall VBA-json parser. First error is Data type conversion error on line rs!OrderDetailID = Order("OrderID"). If i leave that line out then i get error Wrong number of arguments or invalid property assignment on line arrValues = Split(OrderLine, ","). Any help much appreciated. Thanks

Json data retrieved:

{"Order":
[{"ShipLastName":"Bloggs",
"ShipFirstName":"Joe",
"OrderID":"INV1324",
"OrderType":"sales",
"OrderLine":
    [{"Quantity":"1",
    "SKU":"9045200017",
    "OrderLineID":"INV1324-0"}],
"DeliveryInstruction":"",
"ShipPhone":" 6491234567",
"Email":"[email protected]",
"ShippingOption":"Standard Shipping",
"ShipCompany":"Some Company Ltd",
"ShipStreetLine1":"58 Some Street",
"ShipCity":"Some City",
"ShipState":"Some State",
"ShipCountry":"NZ",
"CustomerRef1":"",
"DatePlaced":"2021-06-03 22:26:48",
"OrderStatus":"Pick",
"ShipPostCode":"2103"}],
"CurrentTime":"2021-09-21 05:10:24",
"Ack":"Success"}

    Option Compare Database
    Option Explicit
    Dim arrValues() As String
    Dim I As Integer
    --------------------------
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim JsonText As Object
    Dim Order As Variant
    Dim OrderLine As Variant

    'add order to tblOrderDetails
    Set rs = db.OpenRecordset("tblOrderDetails", dbOpenDynaset, dbSeeChanges)
    Set JsonText = JsonConverter.ParseJson(reader.responseText)
   
    For Each Order In JsonText("Order")
        rs.AddNew
        rs!Date = Format(Now(), "dd/mm/yyyy")
        rs!Time = Format(Now(), "hh:nn")
        rs!ClientID = 123
        rs!OrderNo = Order("OrderID")
        rs!DelPhone = Order("ShipPhone")
        rs!NotifyEmailAddress = Order("Email")
        rs!DelName = Order("ShipCompany")
        rs!DelStreet = Order("ShipStreetLine1")
        rs!DelSuburb = Order("ShipCity")
        rs!DelCity = Order("ShipState")
        rs!DelZipCode = Order("ShipPostCode")
        rs!DelCountryID = DLookup("CountryID", "tblCountries", "CountryCode = '" & Order("ShipCountry") & "'")
        rs!DelContactName = Order("ShipFirstName") & " " & Order("ShipLastName")
        rs.Update
    Next Order
    
    'add products to tblOrdersProd
    Set rs = db.OpenRecordset("tblOrdersProd", dbOpenDynaset, dbSeeChanges)
    
    Set JsonText = JsonConverter.ParseJson(reader.responseText)
        
    For Each Order In JsonText("Order")
        For Each OrderLine In Order("OrderLine")
          rs.AddNew
          rs!OrderDetailID = Order("OrderID")
          arrValues = Split(OrderLine, ",")
          For I = 0 To UBound(arrValues)
             rs!Qty = arrValues(1)
             rs!Productid = DLookup("ProductID", "tblProducts", "ProductCode = '" & OrderLine(arrValues(2)) & "'")
          Next
          rs.Update
       Next OrderLine
    Next Order
    rs.Close

CodePudding user response：

Here is my suggestion, you may need to adjust a bit to get it working...

Option Compare Database
Option Explicit

'//--------------------------
Dim db As DAO.Database
Dim rsOrderDetails As DAO.Recordset
Dim rsOrdersProd As DAO.Recordset
Dim JsonText As Object
Dim Order As Variant
Dim OrderLine As Variant
Dim newID as Long

'add order to tblOrderDetails
Set rsOrderDetails = db.OpenRecordset("tblOrderDetails", dbOpenDynaset, dbSeeChanges)

'add products to tblOrdersProd
Set rsOrdersProd = db.OpenRecordset("tblOrdersProd", dbOpenDynaset, dbSeeChanges)


Set JsonText = JsonConverter.ParseJson(reader.responseText)

For Each Order In JsonText("Order")
    With rsOrderDetails
        .AddNew
        !Date = Format(Now(), "dd/mm/yyyy")
        !Time = Format(Now(), "hh:nn")
        !ClientID = 123
        !OrderNo = Order("OrderID")
        !DelPhone = Order("ShipPhone")
        !NotifyEmailAddress = Order("Email")
        !DelName = Order("ShipCompany")
        !DelStreet = Order("ShipStreetLine1")
        !DelSuburb = Order("ShipCity")
        !DelCity = Order("ShipState")
        !DelZipCode = Order("ShipPostCode")
        !DelCountryID = DLookup("CountryID", "tblCountries", "CountryCode = '" & Order("ShipCountry") & "'")
        !DelContactName = Order("ShipFirstName") & " " & Order("ShipLastName")
        .Update

        '// Now get the newly-allocated autonumber field
        .Bookmark = .LastModified 
        newID = !OrderDetailID
    End With

    For Each OrderLine In Order("OrderLine")
      With rsOrdersProd
          .AddNew

          '// Use the ID from the OrderDetails record
          !OrderDetailID = newID
     
          !Qty = OrderLine("Quantity")
          !Productid = DLookup("ProductID", "tblProducts", "ProductCode = '" & OrderLine("SKU") & "'")
    
          .Update
      End With
    Next OrderLine

    
Next Order

rsOrderDetails.Close
rsOrdersProd.Close