MSQuery Optimization
 

XP Pro, Office 2003

Our data warehouse has exploded with both data and usage. I have a lot of
Excel workbooks that contain querytables (some more than one), and/or VBA
routines that include refreshes of querytables. Most of these were written
under Windows 2000/Office 2000.

Now it takes forever to run many routines/reports because the queries take
forever to run. I have extended the ODBC Timeout to as much as 900 seconds
(15 minutes) and they're still timing out.

I desperately need advice on how to optimize these queries/routines.

Should I:

A: Revamp the queries to a series of smaller (data mining) queries then
re-link by way of vlookups?

B: Run one huge query trying to return all necessary data then break it
out into segments?

C: Do away with the queries altogether and do a straight read/write
operation against the warehouse data tables into Excel cells?

D: or ???

I've got a lot of these queries and really don't want to start down the
wrong path and after a bazillion hours of rewriting this stuff find out
another way was better..

TIA

BAC