Giving some feedback to user, when waiting on SQL SELECT query to finish

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Morris - 19 Sep 2006 10:01 GMT

Hi.

I'm performing a normal SELECT query from Microsoft SQL Server via
ADODB. The line of code which performs the query is a simple:

rsdata.Open sSQL, sConnect, adOpenStatic, adLockReadOnly, adCmdText

Now, the query pulls out anything between 200 and 1600 records, and I
was wondering if I can somehow inform the user that the macro is still
alive, so that I'm sure he doesn't try to kill Excel.

Is it possible to wrap it up so that eg every 5 seconds the macro runs
another line of code saying sth like "Still waiting" or is it
completetly uncontrollable once it's communicating with the SQL server?

Thanks,
Morris

Reply to this Message

Mike Fogleman - 19 Sep 2006 11:50 GMT

I believe that Excel provides some info in the Status Bar at the bottom left
while queries are running. You might ensure that the status bar is visible
before running the query. Read VBHelp on how to capture the status bar
current condition, set it to what you want during macro and putting it back
to the way the user had it originally.
Mike F

> Hi.
>
[quoted text clipped - 13 lines]
> Thanks,
> Morris

Reply to this Message

Tim Williams - 20 Sep 2006 04:08 GMT

You can declare your recordset "with events" and use the events to trigger
message updates (on a userform?)

http://windowssdk.msdn.microsoft.com/en-us/library/ms677579.aspx

Depends really on if the delay is mostly before getting any records, or
between getting the first and last record. At least if you use ADO
asynchronously it won't lock up Excel while they're waiting...

Tim

> Hi.
>
[quoted text clipped - 13 lines]
> Thanks,
> Morris

Reply to this Message