I have a button on a form that runs a code to look for the most recent unshipped order report on Amazon, download it and delimit it into a table and then run a report based off a query pulling from the table. I built into my code that if the most recent report was not finished being run to show a message box. The problem now is that if I request the generation of a report and then later click the button to download it, it will always say the report has not completed running. Then when I close Access and open it again and click the button, it pulls up the report.
I'm thinking this has something to do with how Access is setup to requery. It seems like it is only querying Amazon the first time and then it defaults to the previous response when I click the button again. Should I put some sort of Requery command in my code or would that help?
My code utilizes Amazon's API functionality by submitting get and Post XML statements. I submit the most recently run report ID and Amazon tells me the status of the report. If it has completed, my code downloads and parses it into a table. The only thing I can think of is that I might possibly need to close the XML request, however, I've not seen this being something others included in similar code.
I'm not exactly sure how I would do this. I tried the following but it keeps telling me the report is not finished running, no matter how long I wait. Only after I close the database and reopen will it show that the report was run. I tried a few variations trying to turn off the cache but none of these seemed to work:
'THIS RETRIEVES THE MOST RECENTLY RUN UNSHIPPED REPORT ID
Dim API1 As New XMLHTTP
'change between GET/POST for different types
API1.Open "GET", "https://secure.amazon.com/query/...", False
API1.SetRequestHeader "Authorization", sAuthorization
API1.SetRequestHeader "Content-Type", "text/xml"
API1.SetRequestHeader "Cookie", "x-main=YvjPkwfntqDKun0Q..."
API1.SetRequestHeader "Pragma", "no-cache"
'THIS RETURNS THE RESPONSE AND PULLS OUT REPORT ID
Dim CwsUnshpDwnID As String
CwsUnshpDwnID = API1.ResponseText
MostRecentCWSUsStatus = Mid(CwsUnshpDwnID, 445, 4)
MostRecentCWSUsDownID = Mid(CwsUnshpDwnID, 710, 10)
'THIS DISPLAYS STATUS, EITHER DONE OR NOT READY, NOT READY STOPS MODULE
If MostRecentCWSUsStatus <> "DONE" Then
MsgBox "Most recent report is not ready, please try again later"