Results 1 to 2 of 2
  1. #1
    breakingme10 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153

    Exporting Query As JSON File

    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
    Last edited by breakingme10; 02-19-2020 at 10:30 AM. Reason: Removed some debugging lines

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Thanks for posting. It might be more beneficial to readers if you could supply a copy of the database with anything personal or confidential removed. A simple database with only a few records showing the data, queries etc.
    You are correct that there are few examples of MSAccess vba and JSON as a complete application/demonstration.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Exporting to txt file
    By ngshivakumar in forum Import/Export Data
    Replies: 2
    Last Post: 07-03-2015, 02:51 AM
  2. Replies: 5
    Last Post: 08-27-2014, 01:46 PM
  3. Exporting Query into excel with Todays Date in the file name
    By sam.eade in forum Import/Export Data
    Replies: 19
    Last Post: 01-28-2014, 04:37 PM
  4. Exporting a query to Excel File
    By crowegreg in forum Import/Export Data
    Replies: 2
    Last Post: 08-08-2013, 05:25 PM
  5. Exporting query to Excel file with password?
    By jvera524 in forum Access
    Replies: 0
    Last Post: 12-06-2010, 11:24 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums