Web Query
 

I am looking for the a way to address the following scenario, there are a
number of different options but I would like to hear opinions on the best
approach.

I have an Access database with a table of stock codes
I wish to periodically obtain the price of from the web for each code in the
table.
Once the price has been "read" from the web I would like to store it in a
price history table.


The options to date I have a

Solution: Code in Access to create and Excel object, perform a web query for
each stock code then read and store the value in Access
Problem: Controlling the Excel web query from Access
Problem: Knowing when each web query has completed before attempting to read
the value, store it the loop again

Solution: Code in Excel to access the database, read the codes table,
perform web query, store the value in the database.
Problem: Using two applications as opposed to one as the database is the
main app
Problem: Knowing when each web query has completed before attempting to read
the value, store it the loop again

I have read an article about linking a HTML page to access as a table
however I have been unable to get it to work, however this would be linking
and then relinking the table for each stock that I need to check the price
of.

Comments welcomed and thanks.

Web Query
 

Check out this website:

http://www.vertex42.com/News/excel-web-query.html

It's a primer on Excel web queries and includes a template for pulling
multiple stock prices.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Murphy" wrote:

I am looking for the a way to address the following scenario, there are a
number of different options but I would like to hear opinions on the best
approach.

I have an Access database with a table of stock codes
I wish to periodically obtain the price of from the web for each code in the
table.
Once the price has been "read" from the web I would like to store it in a
price history table.


The options to date I have a

Solution: Code in Access to create and Excel object, perform a web query for
each stock code then read and store the value in Access
Problem: Controlling the Excel web query from Access
Problem: Knowing when each web query has completed before attempting to read
the value, store it the loop again

Solution: Code in Excel to access the database, read the codes table,
perform web query, store the value in the database.
Problem: Using two applications as opposed to one as the database is the
main app
Problem: Knowing when each web query has completed before attempting to read
the value, store it the loop again

I have read an article about linking a HTML page to access as a table
however I have been unable to get it to work, however this would be linking
and then relinking the table for each stock that I need to check the price
of.

Comments welcomed and thanks.