使用VBA將json決議到MSaccess表中的錯誤

請看下面的代碼，使用VBA將資料從json決議到MS Access表中。第一層(Order)作業正常，第二層(OrderLine)出錯了，我不太確定如何從第一張表中獲取OrderDetailID(這是一個自動編號)到第二張表中以便能夠鏈接。我一直在使用我在網上找到的一些代碼，并進行了復制，但有些地方不太對。我在使用Tim Hall的VBA-json決議器。 第一個錯誤是 rs!OrderDetailID = Order("OrderID")這一行的資料型別轉換錯誤。如果我不寫這一行，我就會得到錯誤的引數數量錯誤或無效的屬性分配，這一行arrValues = Split(OrderLine, ",") 。 非常感謝任何幫助。謝謝

檢索到的Json資料：

 Option Compare資料庫
    選項 顯式
    Dim arrValues() As String
    Dim我 As Integer
    --------------------------
    Dim db As DAO.資料庫
    Dim rs As DAO.Recordset
    Dim JsonText As Object
    Dim Order As Variant
    Dim OrderLine As Variant

    '將訂單添加到tblOrderDetails中
    Set rs = db.OpenRecordset("tblOrderDetails"/span>, 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
    
    '將產品添加到tblOrdersProd中
    Set rs = db.OpenRecordset("tblOrdersProd"/span>, 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! Quantity = arrValues(1)
             rs! Productid = DLookup("ProductID", "tblProducts", "ProductCode = '" & OrderLine(arrValues(2)) & "' ")
          下一個。
          rs.Update
       Next OrderLine
    Next Order
    rs.Close

uj5u.com熱心網友回復：

這是我的建議，你可能需要調整一下以使其作業。

Option Compare 資料庫
選項 顯式

'//--------------------------
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"/span>, dbOpenDynaset, dbSeeChanges)

'添加產品到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"/span>)
        !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"/span>)
        ! DelCountryID = DLookup("CountryID", "tblCountries", "CountryCode = ' " & Order("ShipCountry") & " '")
        ! DelContactName = Order("ShipFirstName") & " " & Order(" ShipLastName")
        .更新

        '// Now get the newly-allocated autonumber field.
        .書簽 = .LastModified 
        newID = !OrderDetailID
    結束 與

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

          '// Use the ID from the OrderDetails record
          !"OrderDetailID = newID
     
          !"數量 = OrderLine("數量")
          ! Productid = DLookup("ProductID", "tblProducts", "ProductCode = '"  & OrderLine("SKU") & " '")
    
          .更新
      結束 與
    Next OrderLine

    
NextOrder

rsOrderDetails.Close
rsOrdersProd.Close

標籤：

上一篇：Rails有很多基于最新創建日期記錄的列的排序方法

下一篇：Swift。通用函式回傳一個可選的不會做為？