?
Solved

Progress bar in Access tracking SQL Stored Procedure

Posted on 2007-10-08
1
Medium Priority
?
443 Views
Last Modified: 2013-11-27
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.
0
Comment
Question by:wsturdev
1 Comment
 
LVL 58

Accepted Solution

by:
harfang earned 2000 total points
ID: 20038006
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question