Multiple ADO queries at the same time

[WhytheQ]

Has anybody ever written code using the ADO which fires several SQL
strings at SQL server and then copies and pastes each result into a
different worksheet?

I have to execute 15 queries - each takes 2 minutes. So if I can set
them running at the same time things should only take 2 minutes
whereas if I do one after another I'm looking at tying up Excel for 30
minutes !

Or does anybody understand my problem and know of a possible
workaround.

Any help much appreciated,
Jason.

 

[Jim Thomlinson]

There is no workaround that I am aware of... the code will fire sequentially
and it will require that the data set be returned prior to executing the next
line...

 

[Joel]

Jim: Is is possible to submit the queries and have th output become CSV
files. Then after the CSV files are created read them back into a worksheet.

 

[RB Smissaert]

You are talking about multi-threading then and I don't think that is
possible with VBA alone.
If you have VB6 then maybe you could make 15 .exe files and start them with
Shell as that
doesn't wait till the Shelled process is finished.

RBS

 

[JP]

At the very least, declare your ADODB.Connection object above the sub,
in the Declarations section, and only explicitly set it to Nothing at
the end of your code. That way you'll keep the connection open
throughout.


--JP

 

[Jim Thomlinson]

The issue in in the submit part. As an ADODB connection it will want to wait
for the recordset to return. You could possible called stored procedures on a
server that could run out to a CSV or such.

For doing things similar to what WhytheQ is asking I generally will use a
Citrix session or a remote workstation or even a spare computer to do the
work. It will run for half an hour but it is done in the background somewhere
else in the world so I really don't care.