Solved Importing a Json File into Ms Access Tables (1 Viewer)

nector

Member
Joined
Jan 21, 2020
Messages
416
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

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
 
Since you keep having the same problems, I have a suggestion on how you can find the errors more easily yourself: make your code easier to read and use functions to keep the tasks of the functions clearer.
Also: use clear variable names.
E.g.: rs and rst ... what is their content?

Note: I have already made a proposal for the above construct.
 
Last edited:
E.g.: rs and rst ... what is their content?

rs is the detail lines while rst is the header , see the actual response json below

Kindly ignore rst its sorted out, the header will not come from the internet Json but only the details , this leave us with rs, also note that the line for Dlast is fine no issue at all.

Code:
{
  "resultCd": "000",
  "resultMsg": "It is succeeded",
  "resultDt": "20231120194118",
  "data": {
    "itemList": [
      {
        "taskCd": "2239078",
        "dclDe": "-1",
        "itemSeq": 1,
        "dclNo": "C3460-2019-TZDL",
        "hsCd": "20055900000",
        "itemNm": "BAKED BEANS",
        "imptItemsttsCd": "2",
        "orgnNatCd": "BR",
        "exptNatCd": "BR",
        "pkg": 2922,
        "pkgUnitCd": null,
        "qty": 19946,
        "qtyUnitCd": "KGM",
        "totWt": 19945.57,
        "netWt": 19945.57,
        "spplrNm": "ODERICH CONSERVA QUALIDADE\nBRASIL",
        "agntNm": "BN METRO Ltd",
        "invcFcurAmt": 296865.6,
        "invcFcurCd": "USD",
        "invcFcurExcrt": 929.79
      }
    ]
  }
}


Amended VBA code see below:


Code:
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
On Error Resume Next
Set Company = New Dictionary

stUrl = "http://Localhost:XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"

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 'This is Ok its working
rst("bhfId") = Forms!frmLogin!txtbhfid.Value 'This is Ok its working
rst.Update

'Purchases details to saved in the child table

For Each lineItm In Json("data")("itemList")
    
rs.AddNew ' This where the problem of not inserting imported data is
        
rs("itemSeq") = lineItm("itemSeq")
rs("taskCd") = Itm("taskCd")
rs("dclDe") = Itm("dclDe")
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
End Sub
 
you are showing same json over and over again.
many have answered you in the past and obviously you did not study the code given to you.
it is time for you to learn from them.
 
[OT (or not)]

A code refactoring can be implemented quite well with ChatGPT.
Of course, the code needs to be checked and possibly reworked, but in my opinion it is quite useful as a starting point for refactoring.
See: https://chatgpt.com/share/1f3c6118-8589-44d6-a702-2ff769c8b51b
Note: This is only a refactoring and not an error fixing. If the original code did not work, the new code will not work either.
 

Users who are viewing this thread

Top Bottom