Import data into Access from the web

is this a "one time" thing, or does the database need to read data from that (or various) site(s) every now and then?

if it's a "one time" thing, simply download the file and use the import wizard to read the data into the db.

if it has to be done again and again, you can use the attached code (paste it into a new module) to download the file to a temporary location.  use the code like this:

  Dim res As String
  DeleteCache "http://www.mydomain.com/path/to/datafile.xls"
  res = DownloadFile("http://www.mydomain.com/path/to/datafile.xls", "c:\mytemp\datafile.xls")
  If res = False Then
    Msgbox("File not found.")
  End If

if you always use the exact same path and name for the temporary file, you can set up a linked table in access that points to that file, and you can read the data via that link (the underlying file will change as new files are downloaded, but the link will continue to work).


--bluelizard

Public 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
Public Declare Function DeleteUrlCacheEntry Lib "wininet.dll" Alias "DeleteUrlCacheEntryA" (ByVal lpszUrlName As String) As Long
'Public Const ERROR_SUCCESS As Long = 0
 
Public Function DownloadFile(ByVal sURL As String, _
                             ByVal sLocalFile As String) As Boolean
 
   Dim lngRetVal As Long
   
  'if the API returns ERROR_SUCCESS (0),
  'return True from the function
   DownloadFile = URLDownloadToFile(0&, _
                                    sURL, _
                                    sLocalFile, _
                                    0&, _
                                    0&) = ERROR_SUCCESS
   
End Function
 
Public Function DeleteCache(sURL As String) As Boolean
    DeleteCache = DeleteUrlCacheEntry(sURL)
End Function

Select allOpen in new window

Thanks Bluelizard, that's certainly a likely solution. I hope to download the data once every couple of hours or so.

I had considered the option of creating a scheduled FTP command to download the data from the server to the network before import, which would accomplish things in a similar way. However, I'd assumed there must be an easier way built into access, after all Excel seems to do exactly this using it's WebQuery facility.

ok... another idea is to link the table using an ODBC database connection:

go to the tables, click "New", then "Link Table", then select "ODBC Databases" in the field "Files of Type".  this immediately opens the data source dialog.  there, you might be able to enter the URL of the file.  maybe you first have to set up a driver for the file format that you're using (i think you can do that with control panels > data sources).

(i'm sorry that i cannot give you any further advice on this ODBC topic siince i cannot test it here...).


--bluelizard

I'm afraid I'm floundering with that one - anywhere I try entering a web url in generates an error, 90% of which are not helpful.

"you cannot ure ODBC to import from, export to, or link an external Microsoft Jet or ISAM database table to your database"

or

"the filename entered is invalid"

If I try selecting CSV as the file type and entering the url as the File name I get "Connecting to web server" which is promising, but then "Internet login failure" which is depressing!

cheers

Garve

PS - dear Microsoft, I'd like to reiterate - I'm amazed this is hard!

Sorry to leave this so long - the solution we ended up having to use involved a macro in an excel spreadsheet which uses Excel's Webquery facility to import the data from the server and save it locally. This file is then opened by Access and imported into a table.

Why Access doesn't have the same facilities as Excel I've no idea. However it seems to be a solid way of accomplishing this. Thanks to bluelizard for your assistance.

cheers

Garve

Bluelizard's points helped me to realise that there wasn't an inbuilt method in Access I could use. See next comment.