Progress Meter

[JimP]

I have a pass through query that runs a stored procedure in a SQLServer db.
The sp can take several minutes to run on a large db.

Is there a way to display a progress meter while the sp is running?

 

[Douglas J. Steele]

Not really, because I don't believe you'll get any meaningful indicators
from SQL Server as to how much has been completed.

 

[JimP]

Thanks, I pretty much figured that would be the case. I have a message box
warning them it could take awhile instead.

 

[Douglas J. Steele]

Depending on how you're running your code, using a message box may not be
the best idea, as they're modal (meaning that other processing stops until
the message box is dismissed). You may simply want to create a form with an
appropriate message and display that instead (closing it when you know that
the proc is done).

You might always want to set the hourglass on as a visual reminder.

 

[Tom van Stiphout]

On Sat, 12 Jan 2008 07:08:23 -0600, "JimP"

Douglas is right. However in some situations you can rewrite the sproc
to process a chunk at a time. In this case we pass in the low and high
ID value, and have the sproc only process the given range:
exec long_sproc 1, 1000
'update progress meter
exec long_sproc 1001, 2000
'update progress meter
'etc.

-Tom.

 

[Gunther A Kalempouw]

I found this link from this newsgroup when I'm searching how to create a
report with WORD format from ACCESS.

In this link I've found out that Albert D. Kallal (MVP) also have the
progress bar sample.

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

find for article named SUPER EASY WORD MERGE and download the sample file.

Hope it helps


Cheers,

 

[JimP]

I tried a form and couldn't get it to display until after the sp was
completed - the code went right into the sp. Opening up the form dialog
seemed to be the same as the msgbox.

I'll try some of the other suggestions as well.

 

[Douglas J. Steele]

Put a DoEvents statement between the DoCmd.OpenForm statement and whatever
statement you're using to run the stored procedure.

 

[JimP]

...will do, thanks.

Put a DoEvents statement between the DoCmd.OpenForm statement and whatever
statement you're using to run the stored procedure.