Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-07-15
8
Medium Priority
?
584 Views
Last Modified: 2013-11-27
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.
0
Comment
Question by:PaulCutcliffe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 85
ID: 24857773
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
 

Author Comment

by:PaulCutcliffe
ID: 24858183
Thanks - I'll check it out & report back.
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 1332 total points
ID: 24858273
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:PaulCutcliffe
ID: 24858753
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
 

Author Comment

by:PaulCutcliffe
ID: 24859986
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
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 24860135
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
 

Author Comment

by:PaulCutcliffe
ID: 24860147
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
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 24860564
Oh, that explains. Thanks!

/gustav
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question