Connect to web service in MS Access with VBA

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

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 IfEnd Sub

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