[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Import data into Access from the web

Posted on 2009-04-17
7
Medium Priority
?
1,475 Views
Last Modified: 2012-05-06
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.
0
Comment
Question by:Garve
  • 4
  • 3
7 Comments
 
LVL 14

Expert Comment

by:bluelizard
ID: 24167291
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

Open in new window

0
 
LVL 7

Author Comment

by:Garve
ID: 24167395
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.
0
 
LVL 14

Expert Comment

by:bluelizard
ID: 24167667
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
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 7

Author Comment

by:Garve
ID: 24167961
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!
0
 
LVL 14

Accepted Solution

by:
bluelizard earned 750 total points
ID: 24168178
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).


--bluelizard
0
 
LVL 7

Author Comment

by:Garve
ID: 24419664
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
0
 
LVL 7

Author Closing Comment

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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

868 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