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.

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...
--
HTH...

Jim Thomlinson


"WhytheQ" wrote:

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: 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.

"Jim Thomlinson" wrote:

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...
--
HTH...

Jim Thomlinson


"WhytheQ" wrote:

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.

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.
--
HTH...

Jim Thomlinson


"Joel" wrote:

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.

"Jim Thomlinson" wrote:

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...
--
HTH...

Jim Thomlinson


"WhytheQ" wrote:

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.

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


"WhytheQ" wrote in message
...
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.

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


On Sep 22, 12:43*pm, WhytheQ wrote:
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.