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
559 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

760 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

19 Experts available now in Live!

Get 1:1 Help Now