Import data into Access from the web

I have an Access database on my network.

On a separate website on a completely different server there is some data, available as .csv, .xml or as a simple .html table on a web page.

This data changes from time to time.

What is the best way to easily import this data into my database?

I'm using Access 2002, but could upgrade if needed.
Who is Participating?
yeah i know it's a hassle to set up such a connection.  i did some limited testing, anyway, and i got the same error messages, but what i also noticed was that the connection "wizard" wouldn't set up a *dynamic* connection (to the actual URL, that is), but instead, it downloaded my test text file, stored it on the local PC and linked to *that* file (i.e., the local one).  this wouldn't serve your purpose even if it worked without error messages, since you need the app to actually download the file, right?

so, i guess the scheduled FTP command or the download of the file by access is probably easier... (linking in local files shouldn't be a problem).

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 ""
  res = DownloadFile("", "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).


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

Open in new window

GarveAuthor Commented:
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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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...).

GarveAuthor Commented:
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"


"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!



PS - dear Microsoft, I'd like to reiterate - I'm amazed this is hard!
GarveAuthor Commented:
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.


GarveAuthor Commented:
Bluelizard's points helped me to realise that there wasn't an inbuilt method in Access I could use. See next comment.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.