I need to download some files via HTTP at the press of a button in MS Access - how would I go about doing this?

I am developing an Access database which will download some files from a wholesaler's website, manipulate the data therein, & output some files suitable for importing into an ecommerce website.

The database itself is coming along nicely, and it links to the downloaded files as linked tables.

However, I currently have to manually download the various files from the wholesaler's website, having firstly renamed the previously downloaded ones in case the current ones have problems. As I would like to handover the day to day updating of the website to someone else, I would really like to get Access to automate this process at the push of a button.

How would I go about downloading the files in VBA? I have in the past used the MSXML object, although that was a while ago & from within Lotus Notes which uses LotusScript, which is pretty similar to Visual Basic. I also know there is another object - MSHTML, although I suspect this is more to do with manipulating HTML than communicating via HTTP.

Could someone provide me with a code example of how to download a file to a local folder given the URL of the remote file?

One minor additional question (for an extra 50 points?) - is there any way to programmatically track the progress of the download, or would the application simply be busy until the download was complete? If this question suddenly makes the solution much more complicated by requiring multi-threaded code, then forget it, as it's not important, I just wondered if it were easy.
PaulCutcliffeAsked:
Who is Participating?
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Check this article for code:

http://www.databasejournal.com/features/msaccess/article.php/3513061/Simple-FTP-Methods-from-Microsoft-Access.htm

There's a link in there to this module:

http://go.internet.com/?id=474X1060&url=http%3A//www.mvps.org/access/modules/mdl0015.htm 

I don't believe you can use a progressbar for this ... VBA doesn't report the progress, so there's no way to update a progressbar.
0
PaulCutcliffeAuthor Commented:
Thanks - I'll check it out & report back.
0
Gustav BrockCIOCommented:
Here is how to do via HTTP (not FTP).

/gustav
' Declaration section:
 
Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" ( _
  ByVal pCaller As Long, _
  ByVal szURL As String, _
  ByVal szFileName As String, _
  ByVal dwReserved As Long, _
  ByVal lpfnCB As Long) _
  As Long
 
' Function:
 
Public Function DownloadFile( _
  ByVal strURL As String, _
  ByVal strLocalFilename As String) _
  As Long
  
' Download file or page with public access from the web.
' 2004-12-17. Cactus Data ApS, CPH.
 
' Usage, download a file:
' lngRet = DownloadFile("http://www.databaseadvisors.com/Graphics/conf2002/2002ConferencePicsbySmolin/images/dba02smolin27.jpg", "c:\happybassett.jpg")
'
' Usage, download a page:
' lngRet = DownloadFile("http://www.databaseadvisors.com/conf2002/conf200202.asp", "c:\dbaconference.htm")
 
' Returns 0 if success, error code if not.
' Error codes:
' -2146697210 "file not found".
' -2146697211 "domain not found".
 
' Limitation.
' Does not check if local file was created successfully.
 
  Dim lngRetVal As Long
    
  lngRetVal = URLDownloadToFile(0, strURL & vbNullChar, strLocalFilename & vbNullChar, 0, 0)
  
  DownloadFile = lngRetVal
  
End Function

Open in new window

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.

PaulCutcliffeAuthor Commented:
LSMConsulting, as cactus_data has pointed out, that shows you how to use FTP, which I've done before using the exact same method. I've already wished that the wholesaler made the files available via FTP as that is a nice simple solution, especially as you can get Access to create the FTP script file dynamically. However, the files are only available via HTTP, so I believe I need something more complicated.

cactus_data - thank you for your coded solution - I will try it out & report back.
0
PaulCutcliffeAuthor Commented:
Fantastic, it works!

There is one slight issue, although it may be an issue with the wholesaler's feeds.

All three files I download are .csv files, and one of them's URL is a link directly to the .csv file on the server, & the other two are .asp pages which serve the .csv files on request. Now here's the issue - one of the .asp pages correctly provides the .csv file when requested by your code, whereas the other one simply provides a copy of the .asp file, nicely saved to a .csv file on my disc.

Any idea why this could be happening? Presumably, to the server, MS Access running this code is just an HTTP client making an HTTP request, so how could it work differently from when I use a normal web browser?

I'm happy to allocate points at this stage, although I'm also happy to add 100 points if you can help me with this further issue.

Thanks for your help.
0
Gustav BrockCIOCommented:
If the URL is not for a specific file, the page will be downloaded - as in the example in the in-lice notes of the function.

Thus, you will somehow have to obtain the direct URLs to the other csv files as well.
Without the page or the URL it is hard to give further advise.

/gustav
0
PaulCutcliffeAuthor Commented:
Don't panic Mr Mainwaring! The HTML file returned turns out actually to be the website's home page, to which I was bounced as I wasn't logged in. The wholesaler have already thought of this, & you can add your username & password to the end of the URL as parameters, then you get logged in automatically & then receive the correct file.

So problem solved, points allocated, thanks again for your help.
0
Gustav BrockCIOCommented:
Oh, that explains. Thanks!

/gustav
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
Microsoft Access

From novice to tech pro — start learning today.