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
564 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
  • 4
  • 3
8 Comments
 
LVL 84
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 49

Accepted Solution

by:
Gustav Brock earned 333 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
 

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 49

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 49

Expert Comment

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

/gustav
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction HyperText Transfer Protocol (http://www.ietf.org/rfc/rfc2616.txt) or "HTTP" is the underpinning of internet communication.  As a teacher of web development I have heard many questions, mostly from my younger students who have come to t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

929 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now