I am having a strange issue sending a HTTP request with VBA using "Microsoft.XMLHTTP" function in an Access database
I use this code to retrieve a .CSV file. Everything works fine the first time I run the code after the initial startup of the database.
Public Sub GetUCFiles(myUrl As String, OutputFile As String)
Dim WinHttpReq As Object
On Error GoTo GetUCFiles_Error
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myUrl, False
myUrl = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
Set oStream = CreateObject("ADODB.Stream")
oStream.Type = 1
oStream.SaveToFile OutputFile, 2 ' 1 = no overwrite, 2 = overwrite
Set WinHttpReq = Nothing
On Error GoTo 0
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure GetUCFiles of Module UC Files Import"
But, if the contents of the report on the website changes, it seems to not retrieve the updated data until I restart the database.
When stepping through the code and I get to the line "WinHttpReq.send", the first time it's run, it takes about 3 seconds to process that line. If I rerun the code without restarting the Access database, it moves past that line without delay. I have also tested it where the data should be updated and the .CSV file is different, it will not download unless I restart the access database.