Iteration from Json Format data to Ms Access Table

Question

I need to get the concept clear on how to get or referencing the data from Json formatinto VBA code that will eventually paste the same into Ms Access table. I have the API that provide the Json data but the problem is how to move it the ms access table or pasting it there.

Json Code:

{ 
“Tax”: “889633344”, 
“TaxpayerName”: “Nector”, 
“Addreess”: “westVell”, 
“DateTime”: “20190101203450”, 
“TerminalID”: “USDY95830005”, 
“InvoiceCode”: “KHTY58961803014”, 
“InvoiceNumber”: “000070001”, 
“FCVRCode”: “TYJK58YUIO03938000”, 
“TELEPHONENUMBER”: “+27869300361”, 
“Operator”: “NECTOR”, 
“TaxItems”: [ 
{ 
“TaxLabel”: “A”, 
“CategoryName”: “Standard Rate”, 
“Rate”: 0.16, 
“TaxAmount”: 28.98 
}, 
{ 
“TaxLabel”: “B”, 
“CategoryName”: “MTV”, 
“Rate”: 0.12, 
“TaxAmount”: 96.77 
} 
], 
“TotalAmount”: 309.12, 
“VerificationUrl”: “WWW.nector.info”

The above data need to be moved to the table Ms Access called ("tblEfdReceipts")

VBA Code for receiving

Private Sub StoreData(ADataObject As Object)
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim item As Object
  Set db = CurrentDb
  Set rs = db.OpenRecordset("tblEfdReceipts")
  For Each item In ADataObject
    rs.AddNew
            rs![TAX] = item("TAX")
            rs![TaxpayerName] = item("TaxpayerName")
            rs![Address] = item("Address")
            rs![DateTime] = item("DateTime")
            rs![TerminalID] = item("TerminalID")
            rs![InvoiceCode] = item("InvoiceCode")
            rs![InvoiceNumber] = item("InvoiceCode")
            rs![SpecialCode] = item("SpecialCode")
            rs![TelephoneNumber] = item("TelephoneNumber")
            rs![Operator] = item("Operator")
            rs![Taxlabel] = item("TaxItems")("TaxLabel")
            rs![CategoryName] = item("TaxItems")("CategoryName")
            rs![Rate] = item("TaxItems")("Rate")
            rs![TaxAmount] = item("TaxItems")("TaxAmount")
            rs![VerificationUrl] = item("TaxItems")("VerificationUrl")
            rs.Update
  Next item
  rs.Close
  Set rs = Nothing
  Set db = Nothing

End Sub

I do not know json code, does this come as a file or can there be multiple files? — Max, Dec 12 '19 at 10:17
Have a look to https://github.com/omegastripes/VBA-JSON-parser. There are plenty of questions and answers on SO about the JSonParser. — FunThomas, Dec 12 '19 at 10:19
[This answer](https://stackoverflow.com/a/46245469/4717755) describes how the JSON Parser @FunThomas mentions creates a structure of objects based on the JSON data ingested. Knowing which collection/dictionary/array object to use, you can make the assignments as you indicate in your question. — PeterT, Dec 12 '19 at 12:42

Iteration from Json Format data to Ms Access Table

0 Answers0