Performance V - rapid reports

The purpose of these performance posts is to suggest ways you can try to improve the performance of your Access applications. Some of the tips are just a brain-dump from the author, others have been collated from various Microsoft, FMS and other third-party sources.

Note that some of the tips contradict each other. This is because an optimization in one part of your application may cause a bottleneck in another part. For example, to make a form with subforms open and scroll faster, you may add indexes to each of the fields used to link the form with the subform. This will indeed make the form faster, but the added indexes will make operations like adding and deleting records slower.

Also, there is no such thing as perfect optimization advice. Some of the tips given here may make things run faster on your specific system, some may introduce new performance problems. The point is that optimization is not an exact science. You should evaluate each tip as it applies to your specific application running on your specific set-up. To help in this regard, each tip is rated ■, ■■ or ■■■, with ■■■ being the most advantageous in the author’s experience.

Avoid overlapping controls ■

It takes Access more time to render and draw controls that overlap each other than it does non-overlapping controls.

Think carefully about graphics sparingly ■

Use bitmap and other graphic objects sparingly (or not at all, if you ask me) as they can take more time to load and display than other controls. If you must use graphics, use the Image control instead of unbound object frames to display bitmaps. The Image Control is a faster and more efficient control type for graphic images. Also, if you have to use graphics use bitmaps with the lowest possible color depth. Consider black and white bitmaps where appropriate.

Access Help topic: Add a picture or object

Don't sort report queries ■

Don't base reports on queries with an ORDER BY clause. Reports use their Sorting and Grouping settings to sort and group, so the sort order of the underlying record set is ignored - it just slows the underlying query down.

Avoid expressions and functions ■

Try to avoid reports that sort or group on expressions or functions - this can be very sloooow.

Move expressions ■

Reports require at least one query execution per section. Because of this, your report may run faster if you take calculated fields (expressions) out of the underlying query and move it into the report.

Index appropriately ■■

Consider indexing any fields that are used for sorting/grouping or linking to subreports. Also conder indexing any suberport fields that are used for criteria. Of course, also bear in mind that remember that over-indexing can cause performance bottlenecks when editing, adding and deleting data.

Access Help topic: Indexed property

Minimize the report's dataset ■■

Base reports and subreports on queries instead of tables. By using a query, you can restrict the number of fields returned to the absolute minimum number, making data retrieval faster.

Avoid domain aggregate functions ■

Try to avoid using domain aggregate functions (e.g. DCount, DLookup) in a report's recordsource property. These are generally slow and will cause the report to open and display pages.

Use HasData property ■

Consider using the report's NoData event or HasData property to identify empty reports. You can then display a "sorry, no data" message and close the report.

Access Help topics: HasData property, NoData event

Avoid unnecessary property assignments ■

Only set properties that absolutely need to be set. Properties assignments can be relatively expensive in terms of performance, so make sure you're not setting any properties that don't need to be set.

Avoid the unnecessary ■

If a subreport is based on the same query as its parent report, or the query is similar, consider removing the sub report and placing its data in the main report. While not always feasible, this can speed up the overall report.

Simplify ■

Minimize the number of controls on your report. Loading controls is the biggest performance hit on report load times.

Alternating line colour in a report

Wouldn't it be nice to make your report a bit more readable, perhaps by alternating the background colour of each row? If nothing else, this can save your users having to get a ruler out to read across the lines of your report. But how to do it? Well, there are a couple of things to cover first. We're going to be modifying the Detail section of the report, since I'm assuming that's where you've got your “row” fields. Take a look at the properties of the Detail section and, chances are, you'll find the Back Color property is set to 16777215 - this is white! Consider a colour as being made up of red, green and blue - each of these parameters takes a value between 0 and 255 in the world of Access colour, and the overall colour value is determined like this:

Red value
+ (256 * Green value)
+ (256 * 256 * Blue value)

Red, green and blue all have values of 255 for the colour white - doing the maths reveals the equivalent colour property value to be 255 + (256 * 255) + (256 * 256 * 255) = 16777215 - I know, how intuitive...

So what value do you use for your alternating colour? Well, the easiest way to work this out is to use the Back Color property's builder and pick a colour from a dialog box, then make a note of the number. You'll be pleased to know that pale grey (the alternate colour of choice for all discerning developers) equates to 12632256 - I'll be using this in the example below.

The other thing to point out is that for this to work effectively you must ensure that all visible controls in your Detail section have their Back Style property set to Transparent, otherwise you'll end up with unwanted white blocks all over your nicely shaded background. Anyway, here's the code to do the alternating - it uses the Xor operator within the Detail section's On Format event to do bitwise comparison between the two row colours, and sets the background accordingly.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Const lngColour = 4144959 'i.e. 16777215 (white) - 126322566 (pale grey)
    Detail.BackColor = Detail.BackColor Xor lngColour
End Sub

Hiding the page header on the first page of a report

Chances are you won't want the report header and the page header on the first page as the two sections may have very similar content (captions, run by/when info, and so on). Here's the easiest way I've found of hiding the page header section latter on page one; it makes use of the page header section's OnFormat event, thus:

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
    Me.PageHeaderSection.Visible = Not (Me.Page = 1)
End Sub

This code sets the page header section's visibility to false if the page number is one, otherwise true. Bingo.

Changing the zoom on a report depending on the number of pages

When you preview a report in Access, by default you are zoomed in on it. If the report is only one page long, I quite like to have the whole page showing instead. There is a neat way of doing this with just one line of code in the reports OnPage event. I like to bundle this with code to maximise the report so that it is displayed to its best advantage, like this:

Private Sub Report_Page()
    If Me.Pages = 1 Then DoCmd.RunCommand acCmdFitToWindow
End Sub

Private Sub Report_Close()
    DoCmd.Restore 'Restores the database to its pre-maximised view
End Sub

Private Sub Report_Open(Cancel As Integer)
    DoCmd.Maximize 'Maximises the report preview
End Sub

This does the job, and can be adapted for other purposes too - for example, try replacing acCmdFitToWindow with acCmdZoom150

Anyway, this method replaces a previous fudge that made use of the annoying SendKeys command, which could cause your NumLock to toggle on/off mysteriously if you also used other SendKeys commands in your code, as explained here.

Closing a report automatically if no data exists

There's nothing worse than running a report and it returning a blank page, especially if you choose to print the report rather than preview it. There are two ways round this conundrum: the first is to perform a manual check on the eventual record source the report will use before you open, and then to only open the report is the record count is greater than zero. This can be done using DCount() or with a recordset (preferred, as it is faster). However, I don't like this approach as, assuming there is data, it means running two queries - one to check there is data and a second when you actually open the report. I prefer to make use of the report's OnNoData event instead, like so:

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "No data matches the specified criteria.", vbInformation
    Cancel=True
End Sub

This way, when the report opens if no data is found up pops a message box to that effect, and the report closes. What could be simpler?