Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel-Query-Access Question
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel-Query-Access Question
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 -- -John Please rate when your question is answered to help us and others know what is helpful. "LarryP" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel-Query-Access Question
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. -- Tim Zych www.higherdata.com Compare data in workbooks and find differences with Workbook Compare A free, powerful, flexible Excel utility "LarryP" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel-Query-Access Question
At first glance that looks promising; thanks, John. I'll fiddle around with
it and come back if I run into problems. "John Bundy" wrote: 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 -- -John Please rate when your question is answered to help us and others know what is helpful. "LarryP" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query from Access into Excel cause Access to go to read only | Excel Discussion (Misc queries) | |||
Excel Pivot Table or Access Cross Tab Query Question | Excel Discussion (Misc queries) | |||
Can I use MS Query in Excel like an Append Query in Access | Excel Discussion (Misc queries) | |||
Run Access query from Excel, input value to query being value in c | Excel Programming | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |