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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.