So I searched and searched and there's not a whole lot of examples of how to export a MS Access query into JSON format. I use the VBA-JSON module that's found here. https://github.com/VBA-tools/VBA-JSON
This is the JSON format I need to create followed by the code that I was successful with.
The two recordsets are two queries one for the invoice header information and another query for each line item detail. I opened them both as recordsets, looping through matching the line item details to the header by invoice number.
Once I had the JSON file exported, I then used VBA to post it to the Web service (there are plenty of examples of that around so I'm not adding that code).
I know there may be better ways of going about this, but this is what works for me. I do this with several different web services to automate invoicing for our company on both the AP and AR sides because
we have a sub-company that our main company invoices each month. The AR Invoices generated from the main company are then posted as AP invoices in the sub-company.
Basically, anything in {} goes in a dictionary and anything in [] goes in a collection.
So you have
A main dictionary holding all the data.
A collection of Invoices that is added to the Main dictionary with the key "APInvoices".
That collection contains a dictionary of key/value pairs and two collections. One collection is the Images (my company does not need to add images, so that is why the images are just "" values) and one is the APInvoiceDetails collection. Each of those collections then contain a dictionary holding their key/value pairs.
APInvoiceDetails collection contains multiple dictionaries for Distribution, Job, Equipment and WorkOrder.
Code:
{
"APInvoices": [
{
"Company_Code": "SP1",
"Vendor_Code": "AAAIND",
"Invoice_Number": "AP00000001",
"Invoice_Type_Code": "I",
"Approval_Status": "A",
"GL_Date": "05/15/2017",
"Invoice_Date": "05/15/2017",
"Invoice_Amount": 5000.54,
"Sales_Tax_Amount": 50.01,
"VAT_Code": "",
"Total_VAT_Amount": 0,
"Contract_Number": "",
"Retention_Amount": 500,
"Batch_Code": "ABATCH",
"Payment_Due_Date": "05/30/2017",
"Discount_Due_Date": "05/20/2017",
"Discount_Amount": 250,
"Status": "O",
"Payment_Status": "U",
"Bank_Account_Code": "",
"Check_Number": "",
"Check_Date": "",
"Card_Number": "",
"AP_GL_Account": "012400",
"Cost_Center": "",
"Remarks": "JSON Sample for AP Multi-line import",
"Images": [
{
"Image_Type": "PDF",
"Image_Description": "Image file should be base 64 encoded",
"Document_ID": "",
"Image_File": "**base 64 file encoding**"
}
],
"APInvoiceDetails": [
{
"Distribution": {
"Company_Code": "SP2",
"GL_Account": "012450",
"Cost_Center": ""
},
"Item_Code": "",
"Item_Description": "",
"Unit_Of_Measure": "",
"Quantity": 15,
"Amount": 4450,
"Tax_Code": "1500",
"Job": {
"Job_Number": "",
"Phase_Code": "",
"Cost_Type": ""
},
"Equipment": {
"Equipment_Work_Order": "",
"Equipment_Code": "",
"Equipment_Category": ""
},
"Work_Order": {
"WO_Number": "",
"Unit_Price": 0,
"Equipment": "",
"Component": "",
"Service_Contract": ""
},
"Remark": "This is a sample detail for an AP Multi-line import"
}
]
}
] }
Code:
Public Sub APInvoiceMulti2NestedJSON()Dim dicMain As New Dictionary
Dim colInvoices As New Collection
Dim dicInvoices As New Dictionary
Dim colImages As New Collection
Dim dicImages As New Dictionary
Dim colDetails As New Collection
Dim dicDetails As New Dictionary
Dim dicDist As New Dictionary
Dim dicJob As New Dictionary
Dim dicEQ As New Dictionary
Dim dicWO As New Dictionary
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim FSO As Object
Dim myJSONFileName As String
Dim oJSONFile As Object
Dim oJSONFile2 As TextStream
Dim myTextFileName As String
Dim mydate As String
mydate = format(Date, "yyyy-mm-dd")
myTextFileName = "C:\Users\USERNAME\Documents\DataExports\JSONExportAPInvoice_" & mydate & ".txt"
myJSONFileName = "C:\Users\USERNAME\Documents\DataExports\JSONExportAPInvoice_" & mydate & ".json"
Set FSO = CreateObject("Scripting.FileSystemObject")
Set oJSONFile = FSO.CreateTextFile(myTextFileName)
oJSONFile.Close
Call WaitSeconds(5)
Set oJSONFile2 = FSO.OpenTextFile(myTextFileName, ForWriting)
Set rs1 = CurrentDb.OpenRecordset("qryAPInvoicesMultiLineMasters_2Phase", dbOpenSnapshot)
Set rs2 = CurrentDb.OpenRecordset("qryAPInvoicesMultiLineDetails_2Phase", dbOpenSnapshot)
If Not (rs1.EOF And rs1.BOF) Then
rs1.MoveFirst
Do Until rs1.EOF = True
dicInvoices("Company_Code") = rs1!Company_Code.Value
dicInvoices("Vendor_Code") = rs1!Vendor_Code.Value
dicInvoices("Invoice_Number") = rs1!Invoice_Number.Value
dicInvoices("Invoice_Type_Code") = rs1!Invoice_Type_Code.Value
dicInvoices("Approval_Status") = rs1!Approval_Status.Value
dicInvoices("GL_Date") = rs1!GL_Date.Value
dicInvoices("Invoice_Date") = rs1!Invoice_Date.Value
dicInvoices("Invoice_Amount") = rs1!Invoice_Amount.Value
dicInvoices("Sales_Tax_Amount") = rs1!Sales_Tax_Amount.Value
dicInvoices("VAT_Code") = rs1!VAT_Code.Value
dicInvoices("Total_VAT_Amount") = rs1!Total_VAT_Amount.Value
dicInvoices("Contract_Number") = rs1!Contract_Number.Value
dicInvoices("Retention_Amount") = rs1!Retention_Amount.Value
dicInvoices("Batch_Code") = rs1!Batch_Code.Value
dicInvoices("Payment_Due_Date") = rs1!Payment_Due_Date.Value
dicInvoices("Discount_Due_Date") = rs1!Discount_Due_Date.Value
dicInvoices("Discount_Amount") = rs1!Discount_Amount.Value
dicInvoices("Status") = rs1!Status.Value
dicInvoices("Payment_Status") = rs1!Payment_Status.Value
dicInvoices("Bank_Account_Code") = rs1!Bank_Account_Code.Value
dicInvoices("Check_Number") = rs1!Check_Number.Value
dicInvoices("Check_Date") = rs1!Check_Date.Value
dicInvoices("Card_Number") = rs1!Card_Number.Value
dicInvoices("AP_GL_Account") = rs1!AP_GL_Account.Value
dicInvoices("Cost_Center") = rs1!Cost_Center.Value
dicInvoices("Remarks") = rs1!Remarks.Value
dicImages("Image_Type") = ""
dicImages("Image_Description") = ""
dicImages("Document_ID") = ""
dicImages("Image_File") = ""
colImages.Add dicImages
Set dicImages = Nothing
dicInvoices.Add "Images", colImages
Set colImages = Nothing
If Not (rs2.EOF And rs2.BOF) Then
rs2.MoveFirst
Do Until rs2.EOF = True
If rs2!Invoice_Number.Value = rs1!Invoice_Number.Value Then
dicDist("Company_Code") = rs2!Company_Code.Value
dicDist("GL_Account") = rs2!GL_Account.Value
dicDist("Cost_Center") = rs2!Cost_Center.Value
dicDetails.Add "Distribution", dicDist
Set dicDist = Nothing
dicDetails("Item_Code") = rs2!Item_Code.Value
dicDetails("Item_Description") = rs2!Item_Description.Value
dicDetails("Unit_Of_Measure") = rs2!Unit_Of_Measure.Value
dicDetails("Quantity") = rs2!Quantity.Value
dicDetails("Amount") = rs2!Amount.Value
dicDetails("Tax_Code") = rs2!Tax_Code.Value
dicJob("Job_Number") = rs2!Job_Number.Value
dicJob("Phase_Code") = rs2!Phase_Code.Value
dicJob("Cost_Type") = rs2!Cost_Type.Value
dicDetails.Add "Job", dicJob
Set dicJob = Nothing
dicEQ("Equipment_Work_Order") = ""
dicEQ("Equipment_Code") = ""
dicEQ("Equipment_Category") = ""
dicDetails.Add "Equipment", dicEQ
Set dicEQ = Nothing
dicWO("WO_Number") = ""
dicWO("Unit_Price") = ""
dicWO("Equipment") = ""
dicWO("Component") = ""
dicWO("Service_Contract") = ""
dicDetails.Add "Work Order", dicWO
Set dicWO = Nothing
dicDetails("Remark") = rs2!Remark.Value
colDetails.Add dicDetails
Set dicDetails = Nothing
rs2.MoveNext
Else
rs2.MoveNext
End If
Loop
End If
dicInvoices.Add "APInvoiceDetails", colDetails
colInvoices.Add dicInvoices
Set colDetails = Nothing
Set dicInvoices = Nothing
rs1.MoveNext
Loop
dicMain.Add "APInvoices", colInvoices
Set colInvoices = Nothing
Else
MsgBox ("There are no records")
End If
oJSONFile2.Write ConvertToJson(dicMain, Whitespace:=2)
oJSONFile2.Close
Name myTextFileName As myJSONFileName
MsgBox ("Complete")
Exit_Handler:
Set dicWO = Nothing
Set dicEQ = Nothing
Set dicJob = Nothing
Set dicDist = Nothing
Set dicDetails = Nothing
Set colDetails = Nothing
Set dicImages = Nothing
Set colImages = Nothing
Set dicInvoices = Nothing
Set colInvoices = Nothing
Set dicMain = Nothing
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
End Sub