[AccessD] Read data off a live html table

Wow, that is cool. 


John W. Colby
www.ColbyConsulting.com 


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Tuesday, April 04, 2006 12:01 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Read data off a live html table

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/2002Conferen
cePicsbySmolin/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


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com