First my apologies people you had assisted me sometime back on a similar issue of importing Json from the internet into the MS Access tables, that is the parent and child.
But for whatever reason only the three items have been inserted into the parent table and properly referenced in the child table , BUT THE MAIN JSON IS NOT COMING THROUGH can someone assist to spot where the code mistake is.
Best regards to team members
But for whatever reason only the three items have been inserted into the parent table and properly referenced in the child table , BUT THE MAIN JSON IS NOT COMING THROUGH can someone assist to spot where the code mistake is.
Best regards to team members
Code:
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
On Error Resume Next
Set Company = New Dictionary
stUrl = "http://Localhost:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
Set Request = CreateObject("MSXML2.XMLHTTP")
Company.Add "tpin", Forms!frmLogin!txtTpin.Value
Company.Add "bhfId", Forms!frmLogin!txtbhfid.Value
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 lineItm As Object
Dim Itm As Object
Dim z As Integer
Dim Y As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblImports", dbOpenDynaset, dbSeeChanges)
Set rs = db.OpenRecordset("tblImportDumpdetails", dbOpenDynaset, dbSeeChanges)
Set Json = ParseJson(Request.responsetext)
'i = 1
On Error Resume Next
For Each Itm In Json("data")("saleList")
'Process data.
'Purchases header details to saved in the parent table
rst.AddNew
rst("tpin") = Forms!frmLogin!txtTpin.Value
rst("bhfId") = Forms!frmLogin!txtbhfid.Value
rst("taskCd") = Itm("taskCd")
rst("dclDe") = Itm("dclDe")
rst.Update
'Purchases details to saved in the child table
For Each lineItm In Itm("itemList")
rs.AddNew
rs("itemSeq") = lineItm("itemSeq")
rs("dclNo") = lineItm("dclNo")
rs("hsCd") = lineItm("hsCd")
rs("itemNm") = lineItm("itemNm")
rs("imptItemsttsCd") = lineItm("imptItemsttsCd")
rs("orgnNatCd") = lineItm("orgnNatCd")
rs("exptNatCd") = lineItm("exptNatCd")
rs("pkg") = lineItm("pkg")
rs("pkgUnitCd") = lineItm("pkgUnitCd")
rs("qty") = lineItm("qty")
rs("qtyUnitCd") = lineItm("qtyUnitCd")
rs("totWt") = lineItm("totWt")
rs("netWt") = lineItm("netWt")
rs("spplrNm") = lineItm("spplrNm")
rs("agntNm") = lineItm("agntNm")
rs("invcFcurAmt") = lineItm("invcFcurAmt")
rs("invcFcurCd") = lineItm("invcFcurCd")
rs("invcFcurExcrt") = lineItm("invcFcurExcrt")
rs("ImportID") = DLast("ImportID", "tblImports")
rs.Update
Next
Next
MsgBox "completed Invoice Deatils", vbCritical, "Imports Purchases Done!"
rs.Close
rst.Close
Set rs = Nothing
Set rst = Nothing
Set db = Nothing
Set Json = Nothing
Set Itm = Nothing
Set lineItm = Nothing
End If
