Access Cookbook - Ken Getz [164]
Chapter 8. Optimization
One unavoidable fact of application design is that your application never runs as fast as you'd like it to. Unless you and your users are equipped with the latest and most powerful workstations with huge amounts of memory, performance will be less than ideal. Still, there are many techniques you can use to optimize your application, few of which are easily found in the Access documentation. Although your Access application may never run like that lean and mean dBASE II application you created 15 years ago, you certainly can make it run at an acceptable speed.
This chapter covers several optimizations that enable you to load forms faster, add and change data faster, and speed up your Visual Basic for Applications (VBA) code, for example. It also covers the optimization of queries, as well as multiuser and client/server optimization techniques. In addition, this chapter describes testing techniques that will help you gauge the speed gains of your optimizations.
WARNING
Several of the examples in this chapter take advantage of the DAO type library, rather than the default ADO library used by Access 2002 and Access 2003. Even though it's less "modern," DAO provides greater functionality, and generally better performance. In addition, using DAO makes it possible for these demonstrations to work in earlier versions of Access. If you want to try these techniques in your own applications, make sure you add the DAO reference to your project using the Tools → References menu item from within VBA—it won't be added by default.
8.1. Accelerate the Load Time of Forms
Problem
The first time you open a form in your application, it seems to take forever to load. Is there any way to accelerate this?
Solution
You can radically improve the time it takes to load a form for the first time by preloading your forms when the database is initially opened. You can also decrease the load time for subsequent loadings by hiding instead of closing forms. This solution shows you how to improve form load time using these techniques.
Load the 08-01.MDB database. Note the time it takes for the switchboard form to appear (see Figure 8-1). Make sure that the "Preload and keep loaded forms" checkbox is unchecked; if it's checked, uncheck it, close the database, and start over. Now press one of the command buttons, such as the Orders button, and note how long it takes Access to initially load the form. Close the form.
Figure 8-1. The 08-01.MDB switchboard form
Now check the "Preload and keep loaded forms" checkbox on the switchboard form and close the database. Reload the database and again note the time it takes for the switchboard form to appear. Load the Orders form, again recording the form load time.
You'll see that the switchboard form now takes longer to appear but that the subsequent form load time is significantly shorter. That's because checking the "Preload and keep loaded forms" checkbox and reloading the database flips an internal switch that causes the application to preload its forms (in a hidden state) as the switchboard form is loaded by Access. This lengthens the time it takes for the switchboard form to appear initially. However, because the Orders form is now preloaded, it takes less time for it to appear when you press the Orders command button.
TIP
A switchboard form (or menu form) is an unbound form used for application navigation. Switchboard forms are usually made up of labels and command buttons with an optional picture.
Follow these steps to set up your application to preload its forms:
Create a table for storing the names of the forms you wish to preload. This table (zstblPreloadForms in the sample database) should have a single field, FormName, with a datatype of Text. Switch to datasheet view (see Figure 8-2) and add a row for each form in your application that you wish to preload.
Figure 8-2. Store the list of preloaded forms in the zstblPreloadForms table
Create a switchboard form or edit your existing