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
579 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 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

636 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