Microsoft Access 2010 Bible

Part IV: Professional Database Development

872

The function SetPMeter consists of only three lines — one to display the rectangle and manipu-

late its width, one to display the caption on the bar as it grows inside the rectangle, and one to

repaint the screen each time so that the bar is animated:

Public Function SetPMeter(p As Single)

‘p is percent of total:

Me.PMeterBar.Width = p * Me.PMeter.Width

Me.PMeterBar.Caption = Format(p, “##%”)

Me.Repaint

End Function

Speeding up the progress meter display

This routine is called whenever you want to update the progress meter. Generally, you should call

the progress meter only when it’s likely to be updated. If you know that you have 1,000 records,

you might call the meter every 10 records, but if you have 10,000 records, you might call the

meter every 100 records.

Although this code is simple, it isn’t the best option. In fact, because this code calls the progress

meter for every record, it’s much slower than the built-in progress meter. A better approach is to

call the progress meter every few records:

If (i / lngCounter) * 100 = Int((i / lngCounter) * 100) Then

SetPMeter i / lngCounter

End If

The If statement checks to see whether the calculation of the completion percentage is an integer

(whole number). This calls the progress meter function (SetPMeter) that moves the progress

meter rectangle and displays the percentage completed. It’s called only 100 times to move the rect-

angle; even though the If statement is run 50,000 times, you might wonder why the If statement

is faster. The reality is that the If statement takes very few resources to process, but a function

that changes the width of a rectangle or control, writes to the screen, and then repaints the screen

uses a lot of resources — as evidenced by the time to process falling by 90 percent.

Follow these steps to integrate the Progress Meter into your application:

1. Import the Progress Meter form into your application.


2. Change the code behind the form to interact with your application.

Working with Large Access Databases

When someone mentions large databases in Microsoft Access, he’s generally thinking about a data-

base containing tables holding hundreds of thousands of records. Although this is a large database,

another definition is a database containing hundreds or thousands of objects — tables, queries,

forms, reports, and lots of VBA modules. Although you can sometimes solve data performance