Using MS ACCESS and VBA to create REST/JSON file.

I have been programming in VBA/ACCESS for quite a few yrs but new to JSON and REST. I am not able to get to the web-site <> to see the code you are referring to.   Would it be possible to see a sample of your ACCESS Database to understand how to grab the REST data, using JSON?  Thank you very much!!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Do you need help with the call to retrieve the data or the parsing of the retrieved data or both?

What is the URL from which you are retrieving the data?
vfinatoAuthor Commented:
I'm getting a webpage not available message
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

vfinatoAuthor Commented:
Attached, is a file of the output when I put the URL in a Firefox Browser:
What do you need to get out of those JSON objects?
vfinatoAuthor Commented:
I have never done any web/JSON/REST work w/ MS-ACCESS I am trying to figure out how to do the following:
1. submit a url string using VBA in MS-ACCESS,
2. Retrieve the output from the call that so I can parse the data and do w/ it what I want.
3. How to submit a url string so I can update one of the Parameters for instance in the example below I would like to update the "networkName" value and the "purchaseDate value:

        "id": 1,
        "type": "Asset",
        "assetNumber": "1",
        "contractExpiration": "2014-09-30T07:00:00Z",
        "macAddress": null,
        "networkAddress": null,
        "networkName": null,
        "notes": null,
        "purchaseDate": "2013-12-30T08:00:00Z",
        "serialNumber": null,
        "version": "2010",
        "assetstatus": {
            "id": 2,
            "type": "AssetStatus"
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I'm not sure what the JSON part looks like, but this is an example of calling a web site service and getting a value returned.  This is done with a reference set to Microsoft XML v6.0 lib.


                  ' Capture the CC

                  ' Set the correct URL
                  'strPostURL = ""
110               strPostURL = ""
                  'strPostURL = ""

120               strPostSting = ""
130               strPostSting = strPostSting & "x_login=" & URLEncode(strAPILogin) & "&"
140               strPostSting = strPostSting & "x_tran_key=" & URLEncode(strTransactionKey) & "&"
                  'For debugging.
                  'strPostSting = strPostSting & "x_test_request=" & URLEncode("TRUE") & "&"
150               strPostSting = strPostSting & "x_version=" & URLEncode("3.1") & "&"
160               strPostSting = strPostSting & "x_delim_data=" & URLEncode("TRUE") & "&"
170               strPostSting = strPostSting & "x_delim_char=" & URLEncode("|") & "&"
180               strPostSting = strPostSting & "x_relay_response=" & URLEncode("FALSE") & "&"
190               strPostSting = strPostSting & "x_email_customer=" & URLEncode("FALSE") & "&"

200               strPostSting = strPostSting & "x_type=" & URLEncode("PRIOR_AUTH_CAPTURE") & "&"
210               strPostSting = strPostSting & "x_trans_id=" & URLEncode(rs!CCTransactionID) & "&"

                  ' Additional fields can be added here as outlined in the AIM integration
                  ' guide at:
220               strPostSting = left(strPostSting, Len(strPostSting) - 1)

                  ' We use xmlHTTP to submit the input values and record the response
                  Dim objRequest As New MSXML2.XMLHTTP
230               objRequest.Open "POST", strPostURL, False
240               objRequest.Send strPostSting
250               strPostResponse = objRequest.responseText
                  'Debug.Print strPostResponse
260               Set objRequest = Nothing

                  ' the response string is broken into an array using the specified delimiting character
270               arrResponse = Split(strPostResponse, "|", -1)

280               If arrResponse(0) = 1 Then
                      ' Amount was captured.
                      ' Update order import tracking table
290                   strCommand = "UPDATE tblOrdImportTracking SET CCCapturedAt = '" & Now() & " ' WHERE JobNumber = " & !JobNumber & " AND ExportersOrderNumber = '" & !ExportersOrderNumber & "'"
300                   cnn.Execute strCommand, lngRecordsAffected, adCmdText
310                   If lngRecordsAffected <> 1 Then Stop
320               Else
330                   Stop
                      ' Not captured for some reason - email IT alert list.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim is correct in his assertion that you should use the MSXML2 object.  Here is a simplified version of what Jim posted.  This is my template.
    Dim oXMLHTTP As Object
    Dim strHTML as string
    Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP")
    oXMLHTTP.Open "POST", "https://yourtargetwebsiteURL", False
    oXMLHTTP.Send "your SOAP envelope"
    Do Until oXMLHTTP.ReadyState = 4
    If oXMLHTTP.Status = 200 Then
        strHTML = oXMLHTTP.responsetext
        'add your parsing code here
        'and then push the result into your worksheet
    End If

Open in new window

Do you need all the fields parsed of just some of the fields?
vfinatoAuthor Commented:
I am going to need all the fields parsed
vfinatoAuthor Commented:
What References do I need to set?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
REST is nothing more than an architectural style for doing a web site.  JSON I believe is just a method of formatting the strings based on some stuff in Java,  but in VBA it all boils down to POST and GET.  I think for the JSON part, you could setup a JSON parser based on it's principles, but I'm not sure you'd need to go that far.  Split works pretty well if your needs are no extensive.

And while that routine I posted looks lengthy, most of it is just setting up the string to pass.  The actual back and forth with the web site is only the couple of lines as Mark showed and which you'll see in the middle of the routine I posted.

vfinatoAuthor Commented:
So if I am understanding this correctly.  I get the data by first building the strPostString.
Then sending the strPostString w/ the objRequest.Open and the objRequest.Send strPostString

Then, I capture the returned data with the  strPostResponse = objRequest.responseText

I then parse out the returned data captured in the strPostResponse variable.
Is my thinking correct?

                Dim objRequest As New MSXML2.XMLHTTP
                objRequest.Open "POST", strPostURL, False
                objRequest.Send strPostSting
                strPostResponse = objRequest.responseText
yes.  That is the basic scheme.

Pushing updates is going to be a bit different.
btw...I don't think that VBA-JSON project is complete.
vfinatoAuthor Commented:
I was able to get the returned data using the above examples, then, parse the data as needed.  Now I need to understand how to update a record.
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.