Connect to web service in MS Access with VBA

ms-access, ms-access-2007, vba, web-services

Solution

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

Problem

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.

Original source