Although you may be able to do much to increase the real execution speed of your VBA code and your database calls, there is only so far you can go. So what happens if you have optimized your application for real execution speed and it still appears sluggish ? One option is to increase the application's apparent speed. This is how fast the user thinks the application is, rather than how fast it really is.
Users of an application do not get upset when the application is slow. They get upset when they notice that it is slow! There is a big difference.
Consider the following ways of making an application appear more quickly:
Using a startup form
Removing code from form modules
Pre-loading and hiding forms
Caching data locally
One trusted method of distracting users from the fact that an application is taking a long time to perform some task is to distract them with some fancy graphics. After all, Microsoft, and most all other software suppliers, do it all the time. What happens when you start up Access, Word or Excel? You see a startup form or splash screen.
"Gosh! That's pretty. I wish my application looked so professional!" you think to yourself. And by the time you have snapped out of your reverie, the application has loaded.
If there had been no splash screen you would have probably thought to yourself, "What on earth is happening? Why does it take so long for this application to start?"
Once the database has opened, you can also configure a database form to be the start-up form . A start-up form is simply an Access form that is displayed as soon as a database is opened.
To make a form the start-up form, simply specify its name in the Display Form/Page box on the Tools/Startup... dialog in Access.
The example above will cause frmSwitchboard to be displayed as soon as Dave and Rob's Ice Cream database has been opened (but after the Access splash screen and logon dialog box).
A word of warning - don't overload your startup form with too many controls or complex Load event code or else it will take too long for the form to appear, and you will have defeated the object of the exercise!
Another way that you can distract users - and reassure them that something is happening - is to show a meter displaying the progress of operations that are being performed. The SysCmd function provides a simple way of doing this. There are three steps involved in displaying a progress meter in the status bar of your application:
Initialize the meter and specify its maximum value and text to be displayed
Repeatedly update the meter to show progress
Remove the meter
The following code illustrates one way of displaying a progress meter:
Sub ShowProgress() Dim i As Integer Dim j As Integer Dim intRnd As Integer 'Initialise Progress Meter and set Maximum to 300 SysCmd acSysCmdInitMeter, "Testing...", 300 For i = 0 To 300 'Perform some processing or other... For j = 0 To 10000 'Your real code intRnd = Rnd * 10 + 1 'would replace Next j 'this test loop 'Update Meter to Show Progress SysCmd acSysCmdUpdateMeter, i DoEvents Next i 'Remove Meter from Status Bar SysCmd acSysCmdRemoveMeter End Sub
This procedure causes a gradually filling progress meter to be displayed with the text Testing...
The progress meter is displayed in the status bar of the main Access window, so you will have to switch from the VBA IDE to Access if you want to see it. Note that another alternative to this progress meter is the Common Control that gives progress bars everywhere.
Of course, you do not need to set up a loop to update your progress meter. Instead you could structure your procedure like this:
Sub ShowProgress() 'Initialize Progress Meter and set Maximum to 30 SysCmd acSysCmdInitMeter, "Testing...", 30 'Perform some processing or other... .... 'Update Meter to Show Progress SysCmd acSysCmdUpdateMeter, 5 'Perform more processing... .... 'Update Meter to Show Progress SysCmd acSysCmdUpdateMeter, 10 'And yet more... .... 'Update Meter to Show Progress SysCmd acSysCmdUpdateMeter, 15 . . . 'Remove Meter from Status Bar SysCmd acSysCmdRemoveMeter End Sub
The three constants acSysCmdInitMeter , acSysCmdUpdateMeter, and acSysCmdRemoveMeter , which are used to initialize, update, and remove the status bar, are intrinsic to Access. In other words, they are built into the Microsoft Access 10 Object Library. Since our project has a reference to that library, we do not need to declare these constants anywhere in our code.
Note also that the progress meter will only be shown if the status bar is visible in your database. To make it visible, select Startup from the Tools menu option (available when the database window is active) and tick the Display Status Bar option. You will have to reopen the database for this to take effect.
Users are likely to get irritated if they click a button to open a form and it then seems to take ages for the form to appear. This usually happens when a form has substantial amounts of code in its module, which delays the form's loading. In this situation, you might consider removing the code from the form module and placing it in a standard code module. This will cause the form to load more quickly, as code will only be loaded on demand after the form has opened.
Alternatively, if you find that you use a form frequently and it takes a long time to load and unload, you might choose to load the form during the application's startup and then make it visible and invisible instead of loading and unloading it.
This technique will slow down the startup of your application, but will appear to increase its subsequent performance. You can even delay the loading of the form until after the main form has already appeared. While the user is looking at the main form, the other form can be loading in the background.
This method works well if apparent speed is a coding priority, but it does increase the complexity of the application. You should also bear in mind that having several forms loaded concurrently will increase the application's memory usage.
If you have forms and/or reports that have no code in their modules at all, then you can improve performance by settings their HasModule property to False . This will also reduce the size of your database a little. For forms that only contain links to other forms, consider using macros or hyperlinks rather than VBA, then set the HasModule property to False . One word of warning, if you do set this property to False , using either VBA or the design, Access deletes the code module and any code it contains.
A cache is just an area of memory (or hard disk space) that is used to hold values that have been retrieved from somewhere else, ready to be used again. For example, Windows places the data that comes from your hard disk into a cache made up of an area of memory. Often a program uses the same data over and over again. This way it can just read it from the cache the next time, instead of having to fetch it from the hard disk again. Of course, reading from memory is much quicker and more efficient that reading from a hard disk.
So you can increase an application's performance by caching data in one form or another. In decreasing order of speed (in other words, fastest first), the three methods of data retrieval are:
Reading data from memory (for example variables , arrays)
Reading data from local tables, or external files
Reading data from tables across the network
If you want to increase the perceived speed of your application, think about how you can cache data to 'move it up' a level.
If you keep frequently accessed, non-volatile data (in other words, data that doesn't change much) in a table on a network server, you might consider copying that data to the local client machine to make the application run faster. However, you will need to make sure that whenever data is updated on the server, it is also updated on all client machines as well (and vice versa).
Similarly, if you have data in a lookup table which you frequently access in VBA code, you could create an array to hold that data with the GetRows method. This could make retrieving the data substantially faster. However, it will also increase the memory usage of your application.
Both these methods increase the apparent speed of your application. They may not increase the actual speed, because there will be a performance overhead involved in the process of caching the data in the first place. Remember that caching only works well if you need to read the data several times. If you only read it once, caching will slow your application down.