Using Access VBA to extract remote database data items via MSXML2.XMLHTTP.6.0.

Question

Hi,

I'm trying to write Access VBA subroutine by which I can extract remote database data items using MSXML2.XMLHTTP.6.0. Hereinafter a sample subroutine that results in "Access is denied" when executing WinHttpReq.Send. I need to know why, as windows remote access is tuned on? I also need to know how to save the extracted data item in memory, rather than a file, as to write it to an Access table later on.

Sub ExtractDataItems()

Dim url As String, filePath As String, attempts As Integer
Dim WinHttpReq As Object, oStream  As Object

' DESKTOP-0EKQG3O\SQLExpress, sosdata, cargo, broker are the names of SQL Server, database, record, and field, respectively
url = "http://"MyUrl"/DESKTOP-0EKQG3O\SQLExpress/sosdata/cargo/broker?dt=" & Format(Now(), "yyyymmddhhmmss")
filePath = "c:\sos\SQLServerExtracts.txt"
attempts = 3
Set WinHttpReq = CreateObject("MSXML2.XMLHTTP.6.0")
On Error GoTo TryAgain
TryAgain:
attempts = attempts - 1
Err.Clear
If attempts > 0 Then
WinHttpReq.Open "GET", url, False
WinHttpReq.Send
url = WinHttpReq.ResponseBody

   If WinHttpReq.Status = 200 Then
      Set oStream = CreateObject("ADODB.Stream")
      oStream.Open
      oStream.Type = 1
      oStream.Write WinHttpReq.ResponseBody
      oStream.SaveToFile filePath, 2 ' 1 = no overwrite, 2 = overwrite
      oStream.Close
   End If
Else
   MsgBox ("A4. No internet connection is available.")
End If

End Sub

Best regards.

Said

Answer 1

sosdata, cargo, broker are the names of SQL Server, database, record, and field, respectively

You want to access SQL Server via Http? In which way? Over old & deprecated Soap Endpoint? Have you created such? Or over an other endpoint; which one?

Answer 2

Hi Olaf,

You, as a Microsoft MVP, perhaps know better than myself what MSXML2.XMLHTTP.6.0. is. My understanding is that it is a Microsoft protocol that uses both the SOAP protocol and REST architecture. I'm using a VBA subroutine, which I found on the internet, to extract data items on the web sites. I used it successfully before. The problem now is that I'm using it to extract data items from my SQL Server database called "sosdata" on my computer as a test before I use it with other customers later on. The question is why it fails this time, given the comments you have seen.

Best regards.

Said