Connect to web service in MS Access with VBA

web-services ms-access ms-access-2007 vba
Chris picture Chris · Mar 26, 2013 · Viewed 36.1k times · Source

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.

Answer

nucleon picture nucleon · Mar 27, 2013

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).

Related questions

SOAP vs REST (differences)

I have read articles about the differences between SOAP and REST as a web service communication protocol, but I think that the biggest advantages for REST over SOAP are: REST is more dynamic, no need to create and update UDDI(…

Read More
HTTP POST and GET using cURL in Linux

I have a server application written in ASP.NET on Windows that provides a web service. How can I call the web service in Linux with cURL?

Read More
Call a REST API in PHP

Our client had given me a REST API to which I need to make a PHP call to. But as a matter of fact the documentation given with the API is very limited, so I don't really know how to …

Read More