Introduction.
When processing large volumes of data for Microsoft Access reports, the operation can take anywhere from a few seconds to several minutes, depending on the volume and complexity of the transactions involved. These transactions are typically organized and executed using a combination of Select and Action Queries, orchestrated through Macros or VBA routines to produce the final report output.
If your Action Queries depend on SELECT or CROSSTAB Queries as input sources—particularly when dealing with high transaction volumes—the overall processing time can increase significantly.
In such cases, users may find it difficult to estimate how long the task will take to complete. Over time, with repeated use, they may develop a rough idea of the expected duration.
A common practice is to display the hourglass cursor (DoCmd.Hourglass True) at the beginning of a long-running operation to signal that processing is underway. The hourglass is then reset at the end of the task. However, this visual cue does not provide users with a clear indication of progress or how much time remains until completion.
If the process takes longer than usual—due to factors such as increased transaction volume, heavy network traffic, or other system-related delays—it becomes difficult to determine whether the operation is still actively running or if the system has encountered a hang or failure.
The Quick Solution.
When multiple Action Queries are executed in sequence within a Macro, Microsoft Access briefly displays a Progress Meter on the Status Bar for each query. If the Status Bar is not visible, you can enable it by selecting Options from the Tools menu, navigating to the View tab in the dialog box, and checking the Status Bar option under the Show group. However, this only provides momentary feedback for individual queries and does not indicate the overall progress or estimated time remaining for the entire process.
A Better Approach.
We will explore how to make more effective use of the Progress Meter during data processing tasks by implementing multiple methods. This will allow users of our application to stay informed about the progress, giving them the confidence to momentarily step away or flip through a weekly magazine, while occasionally glancing at the screen to monitor the process.
- Displaying the Progress Meter on the Status Bar
- Displaying the Progress Meter on a Form
- Usage of a transaction countdown method.
Usage of Progress Meter on the Status Bar.
We will use the Order Details table from the Northwind.mdb sample database to demonstrate our example. Our objective is to write a VBA routine that calculates the Extended Price for each entry in this table. If you haven’t already imported the table while working through previous examples, you may do so now. If you're unsure about the exact location of the Northwind.mdb file on your machine, refer to the page Saving Data on Forms Not in Table for guidance.
Steps:
-
Import the Order Details table from the Northwind.mdb sample database into your project.
-
Open the table in Design View, and add a new field
ExtendedPriceat the end of the existing fields.-
Data Type: Number
-
Field Size: Double
-
-
Design a simple form with a Command Button on it, similar to the sample shown below. We’ll use and enhance this form for the next example as well.
Assigning the Procedure to the Command Button.
-
Click on the Command Button to select it.
-
Open the Property Sheet (choose View → Properties if it’s not already visible).
-
Locate the On Click property.
-
Enter the following expression in the On Click property:
Note: Make sure to include the equal sign (
=) at the beginning. Without it, MS Access will interpret the value as the name of a macro instead of a function call. -
Close the form and save it with the name:
ProgressMeter.
-
- Copy and paste the following Code into a Global VB Module of your Project and save it.
Public Function ProcessOrders() Dim db As Database, rst As Recordset Dim TotalRecords As Long, xtimer As Date Dim ExtendedValue As Double, Quantity As Integer Dim Discount As Double, x As Variant, UnitRate As Double On Error GoTo ProcessOrders_Err DoCmd.Hourglass True Set db = CurrentDb Set rst = db.OpenRecordset("Order Details", dbOpenDynaset) rst.MoveLast TotalRecords = rst.RecordCount rst.MoveFirst Do While Not rst.EOF With rst Quantity = ![Quantity] UnitRate = ![UnitPrice] Discount = ![Discount] ExtendedValue = Quantity * (UnitRate * (1 - Discount)) .Edit ![ExtendedPrice] = ExtendedValue .Update If .AbsolutePosition + 1 = 1 Then x = SysCmd(acSysCmdInitMeter, "process:", TotalRecords) Else 'a delay loop to slow down the program 'to view the Progress Meter in action. 'you may remove it. '================================================= xtimer = Timer Do While Timer < xtimer + 0.02 Doevents Loop '================================================= x = SysCmd(acSysCmdUpdateMeter, .AbsolutePosition + 1) End If .MoveNext End with Loop rst.Close x = SysCmd(acSysCmdRemoveMeter) DoCmd.Hourglass False MsgBox "Process completed.", , "ProcessOrders()" Set rst = Nothing Set db = Nothing ProcessOrders_Exit: Exit Function ProcessOrders_Err: MsgBox Err.Description, , "ProcessOrders()" Resume ProcessOrders_Exit End Function - Open the ProgressMeter form in Form View and click the Command Button. You will see the Progress Meter gradually advancing across the status bar. Once it reaches the end, a message will appear indicating that the task has been completed successfully.
We have used Microsoft Access's built-in Function SysCmd() to control and display the Progress Meter on the Status Bar. When the function is first invoked, it is passed the total number of records in the table as the third parameter, which initializes the Progress Meter. Subsequent calls to the function supply the current record number, allowing Access to calculate the percentage of records processed and update the meter accordingly.
The blue indicator on the Progress Meter advances in proportion to the percentage of completion. For large datasets, this means the bar may not visibly move with every single record processed, but instead updates after processing a batch, based on the overall total.
A delay loop has been added to the code for demonstration purposes, allowing users to observe the Progress Meter in action. You may remove these lines when incorporating the routine into your actual project for better performance.
Need a Better Method.
We cannot use this method when sequencing process steps through Macros involving Action Queries. This is because, during execution, Microsoft Access automatically updates the Status Bar to display the progress of each individual query, which overrides and interferes with our custom Progress Meter based on SysCmd().
To overcome this limitation, we must devise an alternative method to track the overall progress of the entire process.
In the next section, we will explore how to use a custom Progress Meter on a Form to visually track the progress of data processing steps executed through a Macro.
Download.
Download Access2007 VersionDownload Access2003 Version
