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.
Who is Participating?
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

How To Create an Asynchronous Connection in ADO

ADO Events

Happy reading,
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.