Connect to web service in MS Access with VBA

Question

Is it possible to connect to a web service (for example send a HTTP Request) via VBA in Microsoft Access? For example, the user clicks a button on a form, then a HTTP Request is sent to a web service that responds with OK.

Has anyone done this before?

Note: VBA, not VB.NET.

As in http://msdn.microsoft.com/en-us/library/office/aa140260(v=office.10).aspx? BTW, MS Access uses VBA, not VB.Net — Fionnuala, Mar 26 '13 at 18:04
Exactly, thanks! I've always searched for VB.net that was the reason why I haven't found anything... — Chris, Mar 26 '13 at 18:21
Another possible solution is to build a COM object in vb.net that consumes and communicates with the web service, and then you set a reference to the vb.net. I think the suggested XMLHttp example is likely the best, but if you have some familiar with vb.net, then you find it generates the class object with a good deal of ease. — Albert D. Kallal, Feb 08 '15 at 02:48

score 14 · Accepted Answer · answered Mar 27 '13 at 01:09

This is code I've used quite successfully with Access 2003. It's from the interwebs, copied and re-copied ages ago. It creates a XMLHttpRequest Object, sends an HTTP GET request, and returns the results as a string.

Public Function http_Resp(ByVal sReq As String) As String

    Dim byteData() As Byte
    Dim XMLHTTP As Object

    Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")

    XMLHTTP.Open "GET", sReq, False
    XMLHTTP.send
    byteData = XMLHTTP.responseBody

    Set XMLHTTP = Nothing

    http_Resp = StrConv(byteData, vbUnicode)

End Function

sReq is the URL; the function returns the response. You may need to make sure ActiveX Data Objects are enabled under your References (in the VBA editor, go to Tools > References).

is it possible to upload/post an image via XMLHTTP object? – munjal Dec 24 '16 at 07:20 — munjal, Dec 24 '16 at 07:20

Adarsh Madrecha · Answer 2 · 2016-02-25T18:13:13.300

2

This is the code , which I used. You need to first reference Microsoft XML V6 for this code to work.

Public Sub GetPerson()
    'For API
    Dim reader As New XMLHTTP60

    reader.Open "GET", "www.exmple.com/users/5428a72c86abcdee98b7e359", False
    reader.setRequestHeader "Accept", "application/json"
    reader.send


    Do Until reader.ReadyState = 4
        DoEvents
    Loop

    If reader.Status = 200 Then
        Msgbox (reader.responseText)
    Else
        MsgBox "Unable to import data."
    End If
End Sub

edited Feb 25 '16 at 18:13

answered Feb 24 '16 at 06:10

Adarsh Madrecha

6,364
11
69
117

3

Thanks... Just 3 years later ;) – Chris Feb 25 '16 at 07:18

score 1 · Answer 3 · answered Mar 26 '13 at 19:08

I have used the "Microsoft Office 2003 Web Services Toolkit 2.01" toolkit (available here) on a few projects. It worked pretty well for me, although I also wrote the web services it was talking to, so I had the luxury of being able to fiddle with both ends of the process when getting it to actually work. :)

In fact, I just upgraded one of those apps from Access_2003 to Access_2010 and the SOAP client part of the app continued to work without modification. However, I did encounter one wrinkle during pre-deployment testing:

My app would not compile on a 64-bit machine running 32-bit Office_2010 because it did not like the early binding of the SoapClient30 object. When I switched to using late binding for that object the code would compile, but it did not work. So, for that particular app I had to add a restriction that 64-bit machines needed to be running 64-bit Office.

Also, be aware that Microsoft's official position is that "All SOAP Toolkits have been replaced by the Microsoft .NET Framework." (ref. here).

Connect to web service in MS Access with VBA

3 Answers3

Linked