Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / May 2008

Tip: Looking for answers? Try searching our database.

Excel-Query-Access Question

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
LarryP - 28 May 2008 19:42 GMT
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 - 28 May 2008 20:35 GMT
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
Signature

-John
Please rate when your question is answered to help us and others know what
is helpful.

> 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.
[quoted text clipped - 10 lines]
> passing back an array with all the answers, something like that.   Any ideas
> welcome.
LarryP - 28 May 2008 21:11 GMT
At first glance that looks promising; thanks, John.  I'll fiddle around with
it and come back if I run into problems.

> 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
[quoted text clipped - 18 lines]
> > passing back an array with all the answers, something like that.   Any ideas
> > welcome.
Tim Zych - 28 May 2008 20:59 GMT
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.

Signature

Tim Zych
www.higherdata.com
Compare data in workbooks and find differences with Workbook Compare
A free, powerful, flexible Excel utility

>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.
[quoted text clipped - 16 lines]
> ideas
> welcome.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.