Run SQL against Access DB/Filter Results
Finally got that to work, Bill, after weeding out some of the inevitable
"what goes inside the parentheses and what doesn't" problems I always
encounter with embedded SQL. ;)
It does offer faster performance, though not as much as I hoped. Running
against a small list of part numbers (~250), it took just over one minute to
pull the specific prices, while running the old way that pulls all available
prices over into Excel (~430,000 at the moment) took about twice that. I
plan to keep testing it against ever-larger lists of parts to see where the
"break-even" point is. In any event, you've moved me forward; thanks again.
"Bill Manville" wrote:
How about creating a WHERE clause along the lines of:
PartNumber IN ('12345', '23456', ..... )
and using that in your query?
Code would be something like this:
Dim C As Range
Dim stWhere As String
For Each C In Range("PartNumbers").Cells
stWhere = stwhere & "'" & C.Value & "', "
Next
stWhere = " PartNumber IN (" & Left(stWhere, Len(stWhere)-2) & ")"
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup
|