Progress bar in Access tracking SQL Stored Procedure

Posted on 2007-10-08
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.
Question by:wsturdev
    1 Comment
    LVL 58

    Accepted Solution

    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,

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    731 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now