Excel to Access query running to slow

Hi all, long time no see! ;)
Wondering if you can help me at all, I have some VBA in Excel that is pulling a query from Access, however it is taking a long time to return despite that it doesn't take a terrible amount of time (<5m) to return if I run the query directly in Access. During this time Excel also chews the processor, taking between 70-100% of the cpu time.

I have tried the same query using an adodb.connection, adodb.command, and also adodb.command running a stored query passing in parameters. The same seems to happen to each!

During the time the query is running it locks up excel completely, I can't do anything except either wait for it to complete (an hour or so later) or kill the process :(

I'll post some code in a minute, however at the moment it's locked up!

Anyone have any ideas what might be causing this or how I could speed it up? I'm using Excel and Access 2000.

~Shiv

There's lots of variables to consider, but a 5 minute query in Access is way too long already. Improve the performance of the Access query, and I'm fairly sure the results will carry down to your Excel app:
http://support.microsoft.com/kb/209126/

Anyone have any ideas what might be causing this or how I could speed it up? I'm using Excel and Access 2000.

Like Tim said, it sounds like your Query is the bottleneck?

While, I suspect while your current instance of Excel is locked up during this process, you can start a new instance of Excel and do as you please with it.

You may want to post your routine and SQL. :)

Thanks for your replies :D
The query itself wasn't the problem, I paid more attention when I re-ran it and it was closer to 1m to run. After lots of messing around trying every possible way to run the query I found the culprit: Calculated fields.
There were a few (4) calculated fields in the query, and that seemed to be what was causing the slowdown. If I run a seperate action query first that creates a temporary table filled with the exact records I want to return, then fetch that table into a recordset, the whole thing runs a lot faster!!
Anyone come across this before?

~Shiv

Yes, it's documented in the article I suggested; you should read it :)

Hehe, that's probably how I figured it out then! Can't rightly remember now! Thanks for the link :)

~Shiv