Web queries

  • Thread starter Thread starter Bill Murphy
  • Start date Start date
B

Bill Murphy

In Excel it is possible to create a web query under Data/Import External
Data/New Web Query. This allows you to bring in a web page and use the data
that is stored in tables on the page. Is there a similar capability in
Access 2000 and Access 2002?

Bill
 
I was hoping that Access allowed you to pull down html pages from a live web
site the way Excel does. This can be done automatically when a spreadsheet
opens by creating a web query on a worksheet.

Bill
 
Have you tried it?
--
Joe Fallon
Access MVP



Bill Murphy said:
I was hoping that Access allowed you to pull down html pages from a live web
site the way Excel does. This can be done automatically when a spreadsheet
opens by creating a web query on a worksheet.

Bill
Click to expand...
 
I could not find any information in Access help, MSDN or the Knowledgebase
about importing from a web page using VBA. But I did find the following
regarding linking in MSDN:

Sub LinkHTML()
Dim dbs As Database
Dim tdfHTML As TableDef
Dim rstSales As Recordset

' Open the Microsoft Access database.
Set dbs = CurrentDb

' Create a TableDef object.
Set tdfHTML = dbs.CreateTableDef("Linked HTML Table")

' Set the connection string to specify the source database type and
' the full path to the file that contains the table you want to link.
tdfHTML.Connect = "HTML Import;" _
& "DATABASE=http://www.federalreserve.gov/releases/h15/update/"

' Set the SourceTableName property to the name of the table you want to
access.
tdfHTML.SourceTableName = "Table1"

' Append the TableDef object to the TableDefs collection to create a
link.
dbs.TableDefs.Append tdfHTML

' Create a Recordset object from the linked HTML table.
Set rstSales = dbs.OpenRecordset("Linked HTML Table")
End Sub

When I run this I get an error 3027 - "Cannot update. Database or object is
read only." I have verified that my mdb is not read only. This error
occurs on the line dbs.TableDefs.Append tdfHTML.

The table on this web page does not have a caption, and the MSDN article
said in this case using Table1 as the name should get the first table on the
page.

Do you have any further thoughts on how to make this work?

Bill
 
Well,
I tried your code on a simple table and got it working in A97 and A2003.

Part of the problem is you jumped right to doing a complex test instead of a
simple one.
Then you couldn't figure out if it was the code or the site or what.

Here it is the procedure (it should look familiar!)

Sub LinkHTML()
Dim dbs As Database
Dim tdfHTML As TableDef
Dim rstSales As Recordset

' Open the Microsoft Access database.
Set dbs = CurrentDb

' Create a TableDef object.
Set tdfHTML = dbs.CreateTableDef("CategoriesHTMLTable")

' Set the connection string to specify the source database type and
' the full path to the file that contains the table you want to link.
tdfHTML.Connect = "HTML Import;" _
& "DATABASE=http://home/someVirtualDirectory/categories.html"
'http://www.federalreserve.gov/releases/h15/update/"

' Set the SourceTableName property to the name of the table you want to
access.
tdfHTML.SourceTableName = "Categories"

' Append the TableDef object to the TableDefs collection to create a
link.
dbs.TableDefs.Append tdfHTML
End Sub


I exported a simple table to html and then reviewed it.
It appears that it exports without column names but the linker expects them
so I added them.
It also appears you need to know the "name" of the table.
In this file it was the caption property.

Here is the HTML file that you can test.

<HTML DIR=LTR>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=Windows-1252">
<TITLE>Categories</TITLE>
</HEAD>
<BODY>
<TABLE DIR=LTR BORDER>
<CAPTION>Categories</CAPTION>
<TR>
<TD DIR=LTR ALIGN=RIGHT>CategoryID</TD>
<TD DIR=LTR ALIGN=LEFT>CategoryName</TD>
<TD DIR=LTR ALIGN=LEFT>Description</TD>
</TR>
<TR>
<TD DIR=LTR ALIGN=RIGHT>1</TD>
<TD DIR=LTR ALIGN=LEFT>Beverages</TD>
<TD DIR=LTR ALIGN=LEFT>Soft drinks, coffees, teas, beers, and ales</TD>
</TR>
<TR>
<TD DIR=LTR ALIGN=RIGHT>2</TD>
<TD DIR=LTR ALIGN=LEFT>Condiments</TD>
<TD DIR=LTR ALIGN=LEFT>Sweet and savory sauces, relishes, spreads, and
seasonings</TD>
</TR>
<TR>
<TD DIR=LTR ALIGN=RIGHT>3</TD>
<TD DIR=LTR ALIGN=LEFT>Confections</TD>
<TD DIR=LTR ALIGN=LEFT>Desserts, candies, and sweet breads</TD>
</TR>
<TR>
<TD DIR=LTR ALIGN=RIGHT>4</TD>
<TD DIR=LTR ALIGN=LEFT>Dairy Products</TD>
<TD DIR=LTR ALIGN=LEFT>Cheeses</TD>
</TR>
<TR>
<TD DIR=LTR ALIGN=RIGHT>5</TD>
<TD DIR=LTR ALIGN=LEFT>Grains/Cereals</TD>
<TD DIR=LTR ALIGN=LEFT>Breads, crackers, pasta, and cereal</TD>
</TR>
<TR>
<TD DIR=LTR ALIGN=RIGHT>6</TD>
<TD DIR=LTR ALIGN=LEFT>Meat/Poultry</TD>
<TD DIR=LTR ALIGN=LEFT>Prepared meats</TD>
</TR>
<TR>
<TD DIR=LTR ALIGN=RIGHT>7</TD>
<TD DIR=LTR ALIGN=LEFT>Produce</TD>
<TD DIR=LTR ALIGN=LEFT>Dried fruit and bean curd</TD>
</TR>
<TR>
<TD DIR=LTR ALIGN=RIGHT>8</TD>
<TD DIR=LTR ALIGN=LEFT>Seafood</TD>
<TD DIR=LTR ALIGN=LEFT>Seaweed and fish</TD>
</TR>
</TABLE>
</BODY>
</HTML>
 
Dear Joe,

I hope you can support me. I tried to link a table in A97
to an HTML file on the internet. But I get the message:
Invalid internet address. Probably because the firewall at
our company becasue at home I can do it. How can I do it
at my work? Many thanks if you could support me.

Kind regards,
Danny Oosterom
 
You have to talk to your Web administrator.
He has blocked the URLs.
He can open them up.
 
testing my e-mail

--
MISTY GREEN
Joe Fallon said:
Well,
I tried your code on a simple table and got it working in A97 and A2003.

Part of the problem is you jumped right to doing a complex test instead of a
simple one.
Then you couldn't figure out if it was the code or the site or what.

Here it is the procedure (it should look familiar!)

Sub LinkHTML()
Dim dbs As Database
Dim tdfHTML As TableDef
Dim rstSales As Recordset

' Open the Microsoft Access database.
Set dbs = CurrentDb

' Create a TableDef object.
Set tdfHTML = dbs.CreateTableDef("CategoriesHTMLTable")

' Set the connection string to specify the source database type and
' the full path to the file that contains the table you want to link.
tdfHTML.Connect = "HTML Import;" _
& "DATABASE=http://home/someVirtualDirectory/categories.html"
'http://www.federalreserve.gov/releases/h15/update/"

' Set the SourceTableName property to the name of the table you want to
access.
tdfHTML.SourceTableName = "Categories"

' Append the TableDef object to the TableDefs collection to create a
link.
dbs.TableDefs.Append tdfHTML
End Sub


I exported a simple table to html and then reviewed it.
It appears that it exports without column names but the linker expects them
so I added them.
It also appears you need to know the "name" of the table.
In this file it was the caption property.

Here is the HTML file that you can test.

<HTML DIR=LTR>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=Windows-1252">
<TITLE>Categories</TITLE>
</HEAD>
<BODY>
<TABLE DIR=LTR BORDER>
<CAPTION>Categories</CAPTION>
<TR>
<TD DIR=LTR ALIGN=RIGHT>CategoryID</TD>
<TD DIR=LTR ALIGN=LEFT>CategoryName</TD>
<TD DIR=LTR ALIGN=LEFT>Description</TD>
</TR>
<TR>
<TD DIR=LTR ALIGN=RIGHT>1</TD>
<TD DIR=LTR ALIGN=LEFT>Beverages</TD>
<TD DIR=LTR ALIGN=LEFT>Soft drinks, coffees, teas, beers, and ales</TD>
</TR>
<TR>
<TD DIR=LTR ALIGN=RIGHT>2</TD>
<TD DIR=LTR ALIGN=LEFT>Condiments</TD>
<TD DIR=LTR ALIGN=LEFT>Sweet and savory sauces, relishes, spreads, and
seasonings</TD>
</TR>
<TR>
<TD DIR=LTR ALIGN=RIGHT>3</TD>
<TD DIR=LTR ALIGN=LEFT>Confections</TD>
<TD DIR=LTR ALIGN=LEFT>Desserts, candies, and sweet breads</TD>
</TR>
<TR>
<TD DIR=LTR ALIGN=RIGHT>4</TD>
<TD DIR=LTR ALIGN=LEFT>Dairy Products</TD>
<TD DIR=LTR ALIGN=LEFT>Cheeses</TD>
</TR>
<TR>
<TD DIR=LTR ALIGN=RIGHT>5</TD>
<TD DIR=LTR ALIGN=LEFT>Grains/Cereals</TD>
<TD DIR=LTR ALIGN=LEFT>Breads, crackers, pasta, and cereal</TD>
</TR>
<TR>
<TD DIR=LTR ALIGN=RIGHT>6</TD>
<TD DIR=LTR ALIGN=LEFT>Meat/Poultry</TD>
<TD DIR=LTR ALIGN=LEFT>Prepared meats</TD>
</TR>
<TR>
<TD DIR=LTR ALIGN=RIGHT>7</TD>
<TD DIR=LTR ALIGN=LEFT>Produce</TD>
<TD DIR=LTR ALIGN=LEFT>Dried fruit and bean curd</TD>
</TR>
<TR>
<TD DIR=LTR ALIGN=RIGHT>8</TD>
<TD DIR=LTR ALIGN=LEFT>Seafood</TD>
<TD DIR=LTR ALIGN=LEFT>Seaweed and fish</TD>
</TR>
</TABLE>
</BODY>
</HTML>
Click to expand...
 
Back
Top