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.