Archive for 16th August 2013

HTTP post in VBA

I have some very nice C# .NET web services that I created and use from some web sites, .NET desktop applications, and iOS apps. But of course, this is not a wide enough range of technologies that I have to deal with, so I needed to figure out how I could call my web service from within an Access database using Visual Basic for Applications (VBA).

The web service just takes in a couple of form variables, chugs through the database looking for the matching records, and returns a string with the pertinent data. Here is the VBA code that I put together to accomplish this task:

Sub Method1()
    Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    URL = ""
    objHTTP.Open "POST", URL, False
    objHTTP.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
    objHTTP.send "formVariable1=value1&formVariable2=value2"
    If objHTTP.Status = 200 Then
        MsgBox objHTTP.responseText
        MsgBox "Failed at getting response from web service:" & objHTTP.Status
    End If
End Sub

The response text is being sent to a message box, but you could just as easily return that string, or better yet, use an XML parser to dig into the data for just what you need. Perhaps I will save that code for another post later.

BTW, Happy Birthday to Julie Newmar, by far the best Catwoman from any Batman live action or animated TV show or movie.