Excel-Query-Access Question

[LarryP]

I saw lots of posts so am fairly sure this is possible, but want to verify
and maybe get pointers to some good how-to articles.

Starting from an Excel spreadsheet with 20 columns of data, take the values
from three columns and use them as criteria to query an Access database. For
each row in the Excel file the query should return one answer (a price) that
then gets plunked into Column 21 for that row. "Sub-question": The
spreadsheet could have as many as a thousand rows, and the Access table being
queried has several HUNDRED thousand, so the query run time could be
considerable if it has to be run once for each row. I'm hoping there's a way
of not having to run the query a thousand times -- passing an array that
holds all the criteria, running the query once on the Access end, then
passing back an array with all the answers, something like that. Any ideas
welcome.

 

[John Bundy]

Yes you can do it, I would read the Select * into an array and then loop
through it for each row. The following site shows you how to connect to a DB
and create a recorset, and then step through that set. Instead of outputting
the data as it steps through, you want to check it to see if it matches your
criteria, if it does, insert it into the cell then start your loop over on
the next criteria. Give it a try and post back with any problems
http://www.vbaexpress.com/kb/getarticle.php?kb_id=889

 

[Tim Zych]

At first glance I like the idea of dumping the XL column data into Access
and let Access do all of the work. Then XL is the presentation layer.

 

[LarryP]

At first glance that looks promising; thanks, John. I'll fiddle around with
it and come back if I run into problems.