4.7 Change and Reset the Access Caption Bar

8.1 Accelerate the Load Time of Forms

8.1.1 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?

8.1.2 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 form load time is significantly shorter. That's because checking the "Preload and keep loaded forms" checkbox and reloading the database flips a 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 much less time for it to appear when you press the Orders command button.

 

A switchboard form (or menu form) is an unbound form used for application navigation. They 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:

1. 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

   

2. Create a switchboard form or edit your existing one. Set the form's AutoCenter property to Yes. Create a new event procedure for the form's Open event. (If you're unsure of how to do this, see "How Do I Create an Event Procedure?" in the Preface of this book.)

   Add the following code to the event procedure:

   Private Sub Form_Open(Cancel As Integer)     ' Preload forms.          Dim db As DAO.Database     Dim rst As DAO.Recordset     Dim varFormName As Variant          On Error GoTo HandleErr          DoCmd.OpenForm acbSplashForm          Set db = CurrentDb(  )          ' Preload the forms listed in zstblPreloadForms.     Set rst = db.OpenRecordset(acbPreloadTable, dbOpenSnapshot)          Do While Not rst.EOF         varFormName = rst!FormName         If Not IsNull(varFormName) Then             DoCmd.OpenForm FormName:=varFormName, _              WindowMode:=acHidden, OpenArgs:="StayLoaded"         End If         rst.MoveNext     Loop          DoCmd.Close acForm, acbSplashForm      ExitHere:     If Not rst Is Nothing Then rst.Close     Set rst = Nothing     Exit Sub HandleErr:     MsgBox "Error " & Err.Number & ": " & Err.Description, , "Form Open"     Resume ExitHere     Resume End Sub

   You can also copy this code from the frmSwitchboard1 form (not the frmSwitchboard form) in 08-01.MDB. (The frmSwitchboard1 version of the form always preloads forms, thus eliminating all the code associated with the "Preload and keep loaded forms" checkbox.)

3. Create an event procedure for the switchboard form's Close event. Add this code to the event procedure:

   Private Sub Form_Close(  )          ' Unload preloaded forms.          Dim db As DAO.Database     Dim rst As DAO.Recordset     Dim varFormName As Variant          On Error GoTo HandleErr          Set db = CurrentDb(  )          ' Unload the forms listed in zstblPreloadForms.     Set rst = db.OpenRecordset(acbPreloadTable, dbOpenSnapshot)          Do Until rst.EOF         varFormName = rst!FormName         If Not IsNull(varFormName) Then             DoCmd.Close acForm, varFormName         End If         rst.MoveNext     Loop      ExitHere:     If Not rst Is Nothing Then rst.Close     Set rst = Nothing     Exit Sub HandleErr:     MsgBox "Error " & Err.Number & ": " & Err.Description, , "Form Open"     Resume ExitHere     Resume End Sub

4. Create the following functions in a global module (or import the basStayLoaded module from 08-01.MDB):

   Public Function acbOpenForm(strFormName As String, _  fStayLoaded As Boolean) As Boolean              ' Open specified form and pass it the     ' StayLoaded argument.          On Error GoTo acbOpenFormErr     If fStayLoaded Then         DoCmd.OpenForm strFormName, OpenArgs:="StayLoaded"     Else         DoCmd.OpenForm strFormName     End If acbOpenFormExit:     Exit Function acbOpenFormErr:     MsgBox "Error " & Err.Number & ": " & Err.Description, _      vbOKOnly + vbCritical, "acbOpenForm"     Resume acbOpenFormExit     Resume End Function Public Function acbCloseForm(frmToClose As Form)     ' If StayLoaded is True, hide the form instead of closing it.              On Error GoTo acbCloseFormErr     If InStr(frmToClose.OpenArgs, "StayLoaded") > 0 Then         frmToClose.Visible = False     Else         DoCmd.Close acForm, frmToClose.Name     End If      acbCloseFormExit:     Exit Function acbCloseFormErr:     MsgBox "Error " & Err.Number & ": " & Err.Description, _      vbOKOnly + vbCritical, "acbCloseForm"     Resume acbCloseFormExit     Resume End Function

5. Throughout your application, when you create code that opens a form and you wish to load that form only once, call the acbOpenForm function from Step 4. If you wish to open a form from code, you can use this syntax:

   Call acbOpenForm("formname", True)

   You can also call the function directly from an event property. In this case, enter the following in the event property:

   =acbOpenForm("formname", True)

   For those forms that you don't wish to keep loaded, change the second parameter of acbOpenForm to False.

6. For each form you are preloading or loading with the acbOpenForm function, add a command button with the caption "Close". Enter the following in the event property for the button's Click event:

   =acbCloseForm(Form)

   Don't place any quotes around the Form argument.

7. Make a copy of the form created in Step 2 and name it frmSplash. This is what's known as a "splash form." Open frmSplash in design view and remove all the command button controls. Also remove all the code behind the form for this copy. In the area where the command buttons used to be, add a label control that contains an initialization message. For example, the label on frmSplash has the attributes shown in Table 8-1. frmSplash is shown in form view in Figure 8-3.

    

   Table 8-1. Properties of frmSplash's lblMessage control

   Property	Value
   Name	lblMessage
   Caption	Initializing...
   BackStyle	Transparent
   BorderStyle	Transparent
   FontName	Arial
   FontSize	14
   TextAlign	Center

    

   Figure 8-3. The splash form, frmSplash

   

8. Open the switchboard form created in Step 2. Open the form's module and add the following constants to the declarations section of the module:

   Const acbcPreloadTable = "zstblPreloadForms" Const acbcSplashForm = "frmSplash"

   Change "zstblPreloadForms" to the name of your table from Step 1. Change "frmSplash" to the name of your form from Step 7.

9. Select Tools Startup to open the database Startup dialog (see Figure 8-4). Select the switchboard form from Step 2 in the Display Form/Page field.

   Figure 8-4. The database Startup dialog

   

10. Close the database and reload it to test your startup procedure and switchboard form.

8.1.3 Discussion

Access forms are stored as binary data in hidden system tables in your database. When you load a form, Access reads data from the system tables to recreate and display that form. This takes time. The solution described here improves the application load time of forms by preloading them when the database is first loaded. This means that the initial application load time will be slower, but users are more tolerant of a long application load time because it is a one-time commitment. As with most performance optimizations, the benefits of this technique are especially noticeable on slow machines.

Prior to Access 95, you had to use an AutoExec macro to initiate some action upon database startup; in recent versions, including Access 2002, you can use the Startup dialog to specify a form to be opened when the database is loaded. This solution takes advantage of the Startup properties, but you also could have used an AutoExec macro.

When the switchboard form opens, the Open event is triggered and the code attached to the form's Open event is executed. Unfortunately, when the Open event procedure is called, the form has not had time to paint itself, so users normally see nothing during the Open event procedure. To remedy this, we created a "splash" form to display during the potentially lengthy process. You don't have to make the splash form the same size as the switchboard form, but in this case, we made the two forms very similar in appearance.

The code to preload the forms is shown here:

Set rst = db.OpenRecordset(acbcPreloadTable) Do While Not rst.EOF     varFormName = rst!FormName     If Not IsNull(varFormName) Then         DoCmd.OpenForm FormName:=varFormName, _          WindowMode:=acHidden, OpenArgs:="StayLoaded"     End If     rst.MoveNext Loop

Each record from the zstblPreloadForms table is read and the named form is loaded in hidden mode. In addition, the form's OpenArgs parameter is passed the string "StayLoaded". You can use the OpenArgs parameter of OpenForm to pass a custom string to a form, much as you pass parameters to a function. This OpenArgs parameter will be used later to decide what to do when the preloaded form is closed.

Once the forms have been loaded in a hidden state, you don't need to do anything special to make them appear. Access is smart enough to make a hidden form visible when you attempt to load it, which makes working with invisible forms easy. However, we include wrapper functions for opening and closing your application's forms in case you want some forms to be treated differently. For example, you may not wish to preload and keep all your forms loaded, because they will take up memory.

Like the Form_Open event procedure attached to the switchboard form, the acbOpenForm function passes the string "StayLoaded" to a form via its OpenArgs argument when you pass True as the function's second parameter. Closing the application form is then handled by acbCloseForm, which is called by the Click event of each form's Close button. This function determines whether to close or hide the form by checking its OpenArgs property, which was passed to the form when it was opened:

If InStr(frmToClose.OpenArgs, "StayLoaded") > 0 Then     frmToClose.Visible = False Else     DoCmd.Close acForm, frmToClose.Name End If

For forms that you do not wish to preload, don't add them to zstblPreloadForms. For forms that you wish to close normally when the Close button is pressed, open them using the following syntax:

=acbOpenForm("formname", False)

If you have enough memory, you may wish to preload all forms and not close them until the application exits. In some situations, however, you may wish to be more selective. By using the preload technique and the acbOpenForm and acbCloseForm functions throughout your application, you can easily change your mind or customize form preloading and form hiding for different requirements.

We did not remove from each sample form the Close button and control box provided by the system. This means that you can use one of these alternate mechanisms to bypass the application-defined Close button (and the acbCloseForm function) and close the form instead of hiding it. Thus, you may wish to set the CloseButton and ControlBox properties of your forms to No to prevent the use of these mechanisms.

You may wish to make zstblPreloadForms a hidden table. You can adjust the hidden property of an object by selecting View Properties.

 

Benchmarking 101

Benchmarking different scenarios is a painstaking process. Because Windows includes a hard disk cache and because Access itself caches data, it's difficult to get fair and accurate timings. Because of caching, the order in which you time things does matter. Avoid jumping to conclusions without repeating the readings several times in different orders. Also, there is no reliable programmatic way to measure the time a form takes to load. Although you can set timers at each of the form's events, Access does some things internally after the last loading event has fired. You will find that the only accurate way to test a form's loading time is to manually test and average the form load using a stopwatch.