Performance VI - mighty macros

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.

Convert! ■■

Convert macros to Visual Basic for Applications (VBA) code. 99 times out of 100, VBA code runs faster than macros (conversion advice).

Converting macros to VBA

There will, inevitably, come a time when you get sick of macros - they're slow, unwieldy and clutter up your database. Who needs them (with the exception of AutoExec and AutoKeys, of course)? Most annoying of all though, macros just don't give you the flexibility you might want - what if you want to add custom error-handling, or perform some action beyond the dazzling array that the RunCommand macro action gives you?

It's time, then, to convert your crappy old macros to shiny VBA. Fortunately, this conversion couldn't be easier. Simply highlight the macro in question in the database window, then click Tools, Macro, Convert Macros to Visual Basic. You'll be prompted on-screen to confirm whether you want to add comments and error-handling. I'd recommend always saying yes to these questions; after all, you can always delete the comments later if they're not appropriate, and the default error-handling saves you a bit of typing when you come to write your own.

There are a couple of things to note though. Each macro you convert will be put in its own module - if you want to simplify things later on, you'll have to manually cut and paste the created VBA code into the module of you choice, then delete the surplus module. These created modules have names of the form “Converted Macro - Your macro name here”.

It's also worth remembering that the VBA this conversion process creates might not always be as slick as you might like. Consider the simplest of examples: a simple macro called mcrOpenMain which has one action, OpenForm, to open frmMain. Converting this in the manner described above produces the following VBA:

'------------------------------------------------------------
' mcrOpenMain
'
'------------------------------------------------------------
Function mcrOpenMain()
On Error GoTo mcrOpenMain_Err

    DoCmd.OpenForm "frmMain", acNormal, "", "", , acNormal

mcrOpenMain_Exit:
    Exit Function

mcrOpenMain_Err:
    MsgBox Error$
    Resume mcrOpenMain_Exit

End Function

Now this isn't too horrendous, and certainly isn't as bad as the VBA that some of the form design wizards (notably the command button one) write. But it does add all the optional command parameters in and does ugly error handling - I like to see the error number too, for a start. My advice then is this: once you're happy that the VBA does what it's supposed to do, delete/edit any surplus comment lines, strip out any unnecessary command parameters and improve the error-handling loop... something like this:

Function mcrOpenMain()
' Converted from mcrOpenMain on 15-Sep-2005 by A. Developer
' Called from menu mnuMain
On Error GoTo mcrOpenMain_Err

    DoCmd.OpenForm "frmMain"

mcrOpenMain_Exit:
    Exit Function

mcrOpenMain_Err:
    MsgBox Err.Number & ": " & Err.Description, vbExclamation
    Resume mcrOpenMain_Exit

End Function

Much nicer, wouldn't you say? Depending on how fussy you are, you might also want to consider renaming the created function so that it doesn't start with “mcr” - but if you do, don't forget to check and change any other references to your nice new function, otherwise they won't run.

Converting macro-based menus

In the good old days (or bad, depending on your viewpoint) before Access 97 if you wanted to create a custom menu, shortcut menu, or toolbar in Access you had to create some macros. Indeed, in this author's opinion this was the only reason to play with macros other than AutoExec and AutoKeys. But times changes, and these days menus and toolbars are created as separate constructs (Tools, Customize, Toolbars). So if you're upgrading from an old version you have a lot of rewriting to do, right?

Wrong! Well, maybe in Access 97 you have, but from Access 2000 onwards the work has been done for you. All you have to do is this. In the database window, click Macros and highlight the name of the macro you want to create a menu or toolbar from. You only need to select the top-level macro; there's no need to select the macro group for each menu that appears on the menu bar. Then, on the Tools menu, select Macro, and then click either Create Menu from Macro, Create Toolbar from Macro or Create Shortcut Menu from Macro, depending on what you want to do.

The important things to remember though is that you can't then go ahead and delete the original macro after creating the new menu/toolbar - this is because it still depends on the original macro to run (unless the original macro only contained AddMenu or RunCommand actions). If you want to do away with the macro altogether, you need to convert the macro actions to VBA code, then modify the menu/toolbar you've just created to use the new VBA functions rather than the old macro actions.

Handling keystrokes with an AutoKeys macro

The AutoKeys macro provides an easy way to set up global shortcut keys within your database. The 'Macro Name' parameter is used to specify the keystroke, whilst 'Action' is used to establish what events happen on pressing the relevant key or keys.

The syntax used for specifying keystrokes in the 'Macro Name' parameter is a subset of that used for the SendKeys statement. Note that you cannot use % to indicate keystroke combinations involved the 'Alt' key. Anyway, here are some examples of permissible syntax:

• {F12} : the F12 function key
• ^S : Ctrl + S
• +P : Shift + P
• +^O : Ctrl + Shift + O
• +^{F4} : Ctrl + Shift + F4

In addition, you can make use of the macro's 'Condition' parameter to only trigger the action if a certain condition is true. A second line against a keystroke can then be used to specify an alternate action if the condition is not true. I tend to use this functionality extensively with a function to determine user group membership so that certain keystrokes are enabled for Admin users but no-one else.

The example AutoKeys macro shown below enables the keystroke combination of Ctrl + N to start the new database dialogue for members of the Admins user group (this is not a built-in function, but bespoke - read all about it here), but displays a warning message box to non-Admin users.

When to use an AutoExec macro

An AutoExec macro is a smart way of running a series of actions every time your database is started. Unless you bypass the start-up (by holding down the shift key), this macro will always be run when the database loads. If you want to perform actions not supported in macros, simply write them as a function in a module and use RunCode from within the AutoExec macro to call them. Also if, for reasons best known to yourself, you don't want to call your start-up macro 'AutoExec', call it something else and then use the /x command line parameter to reference it.

There doesn't seem to be a great deal more to say about AutoExec macros, does there? Well, I will point out that there is a faster alternative if (and only if) you also load a start-up form. If it is suitable to do so, consider putting a VBA equivalent to the AutoExec macro in the OnLoad event of your start-up form. This will speed up your's database load time if the form already has a module (i.e. there is already some VBA on the form). This is because Access only has to load the form and its module into memory, rather than the AutoExec macro, the form and its macro. Okay, so it might only save half a second but, as I have mentioned elsewhere, performance is key to me; as Paul Daniels used to say, every second counts!