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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
Thread Tools | |
Display Modes | |
|
|