Progress bar in Access tracking SQL Stored Procedure

I have a very long running SQL Stored Procedure that I execute from an Access front-end.  I would like to set up a progress bar in Access while the stored procedure is running and came up with this idea.  Anyone have any thoughts?

In the stored procedure, I would include some "go" statements preceded by portions of the SPROC that would would end in updating a value in a record unique to each user.  After issueing the Execute from Access, Access would run a loop and query the value in the user's record to see where it was, and update a progress bar accordingly.

One issue I can see is: in the SPROC, can I carry variables between segments of the SPROC that are separated by the Go Statements?

Another is: how would I issue the execute for the stored procedure from Access and then not wait for it to complete?  If I am updating a value in the user's record, obviously I could put the completion code there and discover it when I am checking on progress from the SPROC -- this would tell me the SPROC is done and I could then continue the code in Access.
LVL 1
wsturdevAsked:
Who is Participating?
 
harfangCommented:
Since you are working from Access, you should look into connection and recordset events. If you declare them in a class module like this:

Dim WithEvents con As ADODB.Connection
Dim WithEvents rec As ADODB.Recordset

You can listen in to any events triggered by them. For instance, the recordset object has a FetchProgress event to create a progress bar.

Both recordsets and the connection can be set to run asynchronously (meaning "not wait for it to complete").

How To Use the ADO FetchProgress and FetchComplete Events
http://support.microsoft.com/kb/262311

How To Create an Asynchronous Connection in ADO
http://support.microsoft.com/kb/194960

ADO Events
http://msdn2.microsoft.com/en-us/library/ms675083.aspx

Happy reading,
(°v°)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.