Problems Sending a HTTP Request with VBA

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
WinHttpReq.send

myUrl = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile OutputFile, 2 ' 1 = no overwrite, 2 = overwrite
    oStream.Close
End If
Set WinHttpReq = Nothing

   On Error GoTo 0
   Exit Sub

GetUCFiles_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure GetUCFiles of Module UC Files Import"
End Sub

Open in new window


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.

Any ideas?
TriMarkAsked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Set oStream = nothing as well.

 But the real problem is the GET.   Change it to POST.

  GET will use cached contents for web pages, where as POST will not.

Jim.
0
 
Gustav BrockCIOCommented:
You have not dim'ed oStream.

If I do so, as Object, your code runs fine here.

Alternatively, use the function DownloadFile here:

Show pictures directly from URLs in Access forms and reports
0
 
TriMarkAuthor Commented:
Gustav,

Thanks for the reply. I guess I need better glasses, totally missed Dimming the oStream.
But, unfortunetaly, that didn't fix it. I also tried the code you supplied, and it reacts the same, which makes me think it may not be the code but some Access setting or a flag that needs to be reset?

The file I'm trying to download is a dynamically made .CSV file that changes constantly while customers are inputting into the website, so data should be different everytime i call the procedure. So typically the first time I run the code after I open the access database, the time it takes to process the line  "WinHttpReq.send" and also the line in your example  "Result = URLDownloadToFile(0, Url & vbNullChar, LocalFileName & vbNullChar, BindFDefault, 0)", is about 3-5 seconds, about the time it would normally take to query the online data and make a report. But on the next time I run it, without restarting access, say 10 seconds later, it processes those lines without delay at all. It writes a new file with the correct date/time, but the data is the same as the first file, like it grabs it from the cache instead downloading a new file. If I restart access, it downloads the new file.

Ideas?
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
Gustav BrockCIOCommented:
DownloadFile does not cache the file.
That is, however, what the "sister" function, DownloadCacheFile, does.
0
 
TriMarkAuthor Commented:
Jim, that was the fix. I knew it was something simple!

Thanks a bunch!
0
 
TriMarkAuthor Commented:
Jim,

I'm trying to understand the syntax a little more, what is the difference between "POST" and "PUT" in this situation?

Mark
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
When dealing with REST interfaces, Post, Get, Put, Patch, and Delete correspond to your basic CRUD operations, which are Create, Read, Update, and delete.

So PUT is an update to something, where as POST is creating something.

In your case, POST was required because you wanted a new version of something, rather than using GET, which was reading something that was already existing.

when you use GET, you get the same thing over and over because nothing is changing in-between the calls.

The details are more involved with actual web development rather than dealing with this from VBA.   Suffice to say from that viewpoint, all you want to know is that typically you'll use a POST call rather than GET when dealing with web sites and consuming data.

Jim.
0
 
TriMarkAuthor Commented:
Jim

Thanks for the information. I appreciate learning from the experts.

Mark
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.