A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Links and Linking
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Run SQL against Access DB/Filter Results



 
 
Thread Tools Display Modes
  #1  
Old August 19th, 2008, 09:55 PM posted to microsoft.public.excel.links
LarryP
external usenet poster
 
Posts: 73
Default Run SQL against Access DB/Filter Results

I have an Excel workbook that contains a list of part numbers, anything from
a few to thousands, that need to be priced. A macro opens a connection to an
Access database of ALL known part numbers and gets ALL the prices, which upon
receipt at the Excel end are loaded into an array for further processing
(using the array gets me around the 65535 row limit, as we have ~300,000 part
numbers). There's also some If-Then wizardry that's performed by the SQL to
select the appropriate price from one of three possibilities.

The processing time to pull all the prices for every run is tolerable, but
if possible I'd prefer to set up the process so only the prices for the parts
in my current Excel list are be pulled. I experimented with a loop that runs
each part in the current list as a separate SQL query, but the run time for
that was far longer than just pulling all prices.

I need to figure out a way to use my list of parts as if it were an Access
table, so I can structure my SQL to only pull the prices I'm currently
interested in, rather than the whole 300K prices. I know exactly how I would
do it if I were working in Access, but this has to start from and run in an
Excel environment -- users may not even have Access installed on their PCs.
Who can set me on the right path?


  #2  
Old August 22nd, 2008, 09:10 AM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default Run SQL against Access DB/Filter Results

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

  #3  
Old August 25th, 2008, 02:14 PM posted to microsoft.public.excel.links
LarryP
external usenet poster
 
Posts: 73
Default Run SQL against Access DB/Filter Results

That looks promising, if I can build a string long enough to hold,
potentially, thousands of part numbers. (Most of the time it'll be in the
3-digit realm, I think, but there's always the worst case to consider.)
Thanks, Bill, I'll re-post after I try it.

"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


  #4  
Old August 25th, 2008, 04:18 PM posted to microsoft.public.excel.links
LarryP
external usenet poster
 
Posts: 73
Default 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


  #5  
Old August 25th, 2008, 04:20 PM posted to microsoft.public.excel.links
LarryP
external usenet poster
 
Posts: 73
Default Run SQL against Access DB/Filter Results

Oh, by the way, I did some Help-surfing before trying this, and discovered
that a string variable can hold a billion-plus characters, so that solved my
initial concern.

"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


  #6  
Old August 29th, 2008, 03:33 PM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default Run SQL against Access DB/Filter Results

Pleased you got it working.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:12 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.