[AccessD] Read data off a live html table

Gustav Brock Gustav at cactus.dk
Tue Apr 4 11:00:42 CDT 2006


Hi all

Maybe I can answer this myself ...

I've looked around and live connection seems just not to work. The reason Excel can do it, is that it doesn't ... you ask it to requery and it reads in the page and the data and saves them in the worksheet for a static view.

For Access that static view is an external file. Thus download the page to a file and link that as a table.
However, the download can be semi-automated. 
You can use this function to download the file (takes less than a second) before opening the linked table in Access - no relink is needed:

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

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.htm", "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

You could download the file when launching the app or a form, or if a more frequent update is needed, have a timer running doing it. When the file is written you cannot have the table linked to it open in Access so some sort of error handling is needed. That could be avoided by just opening the linked table for updating the data in another table the moment after a fresh download.

/gustav

>>> Gustav at cactus.dk 04-04-2006 16:15:08 >>>
Hi all

Why can Excel read data from a live web page table just like that while Access can't?

Go to this page: 
  http://www.hdnl.co.uk/tracker.aspx?UPI=806290025850a 

In IE, right-click on the table and choose Import in Excel, and in two seconds Excel opens with a worksheet with the linked data.

You can edit the query Excel uses and save it. This is it:

<iqy>
WEB
1
http://www.hdnl.co.uk/tracker.aspx?UPI=806290025850a 

Selection=1
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
</iqy>

If you use IE to save the page as a single html page, Access can link to that fine, so data is valid.
Any tricks for the live link? 

/gustav





More information about the AccessD mailing list