How to post data from Ms access to Json using API



  • Dear All;

    Kindly see how you can assist me to send data from Ms Access to Json API, I have tried the code below from your site it works very when importing the data . What about sending the data using "POST" how can it be done????

    Private Sub CmdEmp_Click()
    Dim http As Object
    Dim JSON As Object
    Dim i As Integer
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set http = CreateObject("MSXML2.XMLHTTP")
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Contact")
    http.Open "GET", "http://jsonplaceholder.typicode.com/users", False
    http.send
    Set JSON = ParseJson(http.responseText)
    i = 2
    For Each Item In JSON
       
        With rs
            .AddNew
            ![ID] = Item("id")
            ![FirstName] = Item("name")
            ![UserName] = Item("username")
            ![Email] = Item("email")
            ![City] = Item("address")("city")
            ![Phone] = Item("phone")
            ![WebSite] = Item("website")
            ![Company] = Item("company")("name")
            .Update
        End With
        i = i + 1
    Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set JSON = Nothing
    Set http = Nothing
    
    MsgBox ("complete")
    

    End Sub


  • administrators

    @nector said in How to post data from Ms access to Json using API:

    http.Open "GET", "http://jsonplaceholder.typicode.com/users", False
    http.send

    Hi @nector You should be able to post in the following way -

    http.Open "POST", "https://apiurl.com", False
    http.send "id=1&name=codingislove"
    

    If it is a JSON API then you have convert the data into JSON string and send.



  • Thank so much let me work on the data for testing.

    Regards

    Chris



  • Sorry I got stuck again on Json String format , how is it done??????

    Dear Experts

    Kindly see how you can help again in my final part of this Ms Access /Json project, now I want the data in the query below to be posted to the site as per link in the VBA. The problem I have right now is how to format the data in a query into Json so that it can be posted successfully
    Qry1

    SELECT tblInvoice.Customer, tblCustomers.TaxID, tblCustomers.Address, tblInvoice.INV, tblInvoice.InvoiceDate, tblInvoicedetails.Product, tblInvoicedetails.Qty, tblInvoicedetails.Price, tblInvoicedetails.VAT, (([Qty][Price])(1+[VAT])) AS TotalPrice
    FROM tblProducts INNER JOIN ((tblCustomers INNER JOIN tblInvoice ON tblCustomers.ID = tblInvoice.Customer) INNER JOIN tblInvoicedetails ON tblInvoice.INV = tblInvoicedetails.INV) ON tblProducts.PDID = tblInvoicedetails.Product
    WHERE (((tblInvoice.INV)=[Forms]![frmInvoice]![INV]));

    Below is the VBA that I have prepared requiring completing the Json data formatting:

    Private Sub CmdSales_Click()
    Dim http As Object
    Dim db As Database
    Dim rs As Recordset
    Set db = CurrentDb
    Dim JSON As Object
    Dim i As Integer
    Dim item As Object
    Set rs = db.OpenRecordset("SELECT * FROM Qry1 Where Qry1.ID = " & Me.INV, dbOpenDynaset, dbSeeChanges)
    http.Open "POST", "http://jsonplaceholder.typicode.com/users/?id=" & Me.INV, False
    http.send
    Set JSON = ParseJson(http.responseText)
    i = 2
    End Sub

    Kindly see how this can be done!


Log in to reply
 

Looks like your connection to Codingislove Forum was lost, please wait while we try to reconnect.