Access Cookbook

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Aa188218.accesscook_xs(en-us,office.10).gif

Chapter 8 Excerpt: Make Slow Forms Run Faster

Ken Getz, Paul Litwin, and Andy Baron
Microsoft Corporation

November 2002

Applies to:
    Microsoft® Access

Buy this book

Summary: This article presents an excerpt from the book Access Cookbook by Ken Getz, Paul Litwin, and Andy Baron. Learn how to change Access forms to load and display faster. (10 printed pages)

Contents

Problem
Solution
Discussion

Problem

You are not happy with the speed at which your forms load and display. How can you change your forms so they will load and display faster?

Solution

Access gives you a lot of flexibility to develop dynamite-looking forms. Unfortunately, Access also makes it easy to create forms that run painfully slowly. Solution 8.1 explained how you can speed up the loading time of all forms by preloading them. This solution discusses how to track down and fix various performance bottlenecks, thus improving form execution performance. We also discuss the use and misuse of graphic elements and combo and list box controls.

You should consider several potential issues when analyzing your forms for performance. We discuss here two common performance bottlenecks: controls involving graphic or memo field data, and combo and list box controls.

Graphic and memo controls

Load the 08-02a.MDB database. Open the frmCategoriesOriginal form (see Figure 8-5). This form, although attractive, loads slowly and has a noticeable delay on slower machines when moving from record to record. Now open frmCategoriesStep3, which is the final version of the form after various optimizations have been applied to it (see Figure 8-6). Its load and execution times should be noticeably faster.

Figure 8-5. The original form, frmCategoriesOriginal, is slow.

Aa188218.odc_cookbookchapter8_02(en-us,office.10).gif

Figure 8-6. The final form, frmCategoriesStep3, is faster.

Follow these steps to improve the performance of forms that include unbound graphic controls or bound controls that hold OLE or memo fields:

  1. Open the problem form in design view. If you have any unbound object frame controls (also know as unbound OLE controls) that are used to store fixed graphic images, change them to image controls by right-clicking on the object and selecting Change To, Image (see Figure 8-7). The frmCategoriesStep1 form in the 08-02a.MDB sample database is identical to frmCategoriesOriginal except that ctlLogo has been converted from an unbound object frame control to an image control.

    Aa188218.odc_cookbookchapter8_03(en-us,office.10).gif

    Figure 8-7. Changing an unbound object frame control to an image control

  2. If you created a watermark for the form, consider removing it. To do this, select the word "bitmap" in the form's Picture property, press the Del key, and answer Yes to the confirming dialog. The frmCategoriesStep2 form in 08-02a.MDB is identical to frmCategoriesStep1, except that we deleted the watermark.

  3. If your form contains any bound controls that hold either OLE or memo fields, consider moving the controls to a second page of the form. In the final version of the Categories form, named frmCategoriesStep3 (Figure 8-6), we moved the ctlDescription and ctlPicture controls to a second page.

Combo and list box controls

Load the 08-02b.MDB database. Open the frmSurveySlow form. This form contains a combo box control, cboPersonId, that has as its row source a SQL Select statement that pulls in 15,000 rows from the tblPeople table. Load time for the form is slow because Access has to run the query that supplies the 15,000 rows to cboPersonId. Tab to the cboPersonId control and type "th" to search for the name "Thompson, Adrian" (see Figure 8-8). Note the long delay before the "th" list of records appears. Now open the frmSurveyFast form (see Figure 8-9); its load time is significantly faster. Press the ">" command button to open the frmPersonPopup form. Type "th" in the first field and press Tab. After a short delay, you'll be able to select "Thompson, Adrian" from the drop-down list as shown in Figure 8-10. Press the OK button, which will drop the chosen name back into the txtPersonName text box on frmSurveyFast.

Aa188218.odc_cookbookchapter8_04(en-us,office.10).gif

Figure 8-8. The cboPersonId combo box in frmSurveySlow is very slow.

Aa188218.odc_cookbookchapter8_05(en-us,office.10).gif

Figure 8-9. In frmSurveyFast, the combo box is replaced with a text box and command button.

Aa188218.odc_cookbookchapter8_06(en-us,office.10).gif

Figure 8-10. Selecting a name from the drop-down list is much faster

Follow these steps to improve the speed of forms containing combo or list boxes that must display a lot of information:

  1. Make a copy of the problem form and open the copy in design view. Select the slow combo or list box control. Right-click on the control and select Change To, Text Box.

  2. Create a new unbound pop-up form with the property settings shown in Table 8-2. Leave the remaining property settings at their defaults. In the sample database, this form is named frmPersonPopup.

    Property Setting
    ScrollBars Neither
    RecordSelectors No
    NavigationButtons No
    AutoResize Yes
    AutoCenter Yes
    PopUp Yes
    Modal Yes
    MinMaxButtons None

    Table 8-2. Property settings for the pop-up form

  3. Create four unbound controls on this form: a text box, a combo box and two command buttons. In the sample database, we created the controls shown in Table 8-3. The text box will be used to limit the number of items in the combo box, using the parameter query created in Step 4.

    Control type Control name Notes
    Text box txtChar Limits the values in the row source of the combo box
    Combo box cboPersonID Uses the parameter query created in Step 4 as its row source
    Command button cmdOK Hides form
    Command button cmdCancel Closes form

    Table 8-3. The controls on frmPersonPopup

  4. Create a new query that will serve as the row source for the combo box of the pop-up form. If you used a query as the source for the combo or list box on the original form, you should be able to modify its design. Add the necessary fields to the query. Add a parameter to the form that limits the rows based on a value typed into the text box on the pop-up form. Choose any sort fields. In the sample database, we created the qryPersonComboBox query with the fields shown in Table 8-4. Save and close the query.

    Query field Sort Criteria
    PersonId (None) (None)
    FullName: [LastName] & ", " & [FirstName] (None) (None)
    LastName Ascending Like [Forms]![frmPersonPopup2]![txtChar] & "*"
    FirstName Ascending (None)

    Table 8-4. The fields in qryPersonComboBox

  5. Reopen the pop-up form created in Steps 2 and 3. Set the Enabled property of the combo box to No. Set the RowSource property to point to the query created in Step 4. In the sample database, we set the properties of the cboPersonId combo box to the values in Table 8-5.

    Property Setting
    Enabled No
    RowSourceType Table/Query
    RowSource (Blank)
    ColumnCount 2
    ColumnHeads No
    ColumnWidths 0";2.5"
    BoundColumn 1
    ListRows 8
    ListWidth 2.5"

    Table 8-5. Property settings for cboPersonId

  6. Create a new event procedure for the text box's Change 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 txtChar_Change(  )
    
        If Not IsNull(Me!txtChar.Text) Then
            Me!cboPersonID.Enabled = True
        Else
            Me!cboPersonID.Enabled = False
        End If
    
    End Sub
    
  7. Change txtChar to the name of your text box and cboPersonId to the name of your combo box.

  8. Create a new event procedure for the text box's AfterUpdate event and add the following code to it:

    Private Sub txtChar_AfterUpdate(  )
    
        Dim ctlPersonId As ComboBox
        Dim ctlChar As TextBox
    
        Set ctlPersonId = Me!cboPersonID
        Set ctlChar = Me!txtChar
    
        If Not IsNull(ctlChar) Then
            ctlPersonId.RowSource = "qryPersonComboBox"
            ctlPersonId.SetFocus
            ctlPersonId.Dropdown
        End If
    
    End Sub
    
  9. Change txtChar to the name of your text box, and cboPersonId to the name of your combo box. Change qryPersonComboBox to the name of the query you created in Step 4.

  10. Create the following new event procedure for the OK command button's Click event:

    Private Sub cmdOK_Click(  )
        Me.Visible = False
    End Sub
    
  11. Create the following new event procedure for the Cancel command button's Click event:

    Private Sub cmdCancel_Click(  )
        DoCmd.Close acForm, Me.Name
    End Sub
    
  12. Save the pop-up form and close it.

  13. Reopen the form from Step 1 in design view. Add a button called cmdPopup to the right of the text box. Add the following event procedure to cmdPopup's Click event:

    Private Sub cmdPopup_Click(  )
    
        Const acbcPopup = "frmPersonPopup"
    
        ' Open up pop-up form in dialog mode.
        DoCmd.OpenForm acbcPopup, WindowMode:=acDialog
    
        ' Check if form is still loaded.
        ' If yes, then OK button was used to close pop-up.
        If SysCmd(acSysCmdGetObjectState, acForm, acbcPopup) <> 0 Then
            Me!PersonID = Forms(acbcPopup)!cboPersonID
            DoCmd.Close acForm, acbcPopup
        End If
    
    End Sub
    
  14. Change frmPersonPopup to match the name of the pop-up form. Change PersonId and cboPersonId to the names of the appropriate controls.

Discussion

When you have a form that loads and executes slowly, you need to analyze the form and weigh the advantages and disadvantages of using graphic features. After a careful analysis of the frmCategoriesOriginal form in the 08-02a.MDB database, we made several changes.

First, we changed the unbound object frame control to an image control. The OLE-based object frame control can be used to hold graphic images, sound and other OLE-based data such as Excel spreadsheets or Word documents. But if you need to display only an unbound bitmap, you're better off using the more resource-conservative image control.

Second, we removed the form watermark, as this feature slows down form execution slightly. The improvement in performance depends on the color-depth of the removed image and the speed of your machine.

Finally, we created a second page and moved the text box bound to the memo field and the bound object frame bound to the OLE field to this second page. These field types (memo and OLE) are stored separately from the rest of the fields in a record and thus require additional disk reads to display. Fortunately, Access does not fetch these potentially large fields from the database unless they are visible on the screen. By placing them on the second page, you can quickly navigate from row to row without having to fetch the memo or OLE data. When you need to view the data in the fields, you can easily flip to the second page of the form.

The frmSurveySlow form in 08-02b.MDB contains a combo box, cboPersonId, bound to a 15,000-row table. This makes form load and combo box list navigation slow. Combo and list box controls are excellent for allowing users to choose from a list of values and work well with a small number of list rows. However, they perform poorly when the size of the list exceeds a few thousand rows, even with very fast hardware.

We were able to improve the load time of the survey form significantly by limiting the rows in the person combo box. This was done using a pop-up form containing the same combo box control, but linked to a text box control that filtered the combo box's rows via a parameter query. Using a little VBA code, we disabled the combo box control until at least one character was entered into the text box. In this way, we reduced a 15,000-row combo box to, on average, 577 rows (15000 / 26), and that's when only the minimum number of characters (one) is typed into the text box. You could increase performance by waiting for at least two or even three characters, rather than filling the list after the user has typed only one letter.

Besides reducing the number of rows in the row source for cboPersonId, two other improvements were made to boost combo box performance. On the original frmSurveySlow form, a SQL statement was used as the row source for the combo box; the cboPersonId combo box on the pop-up form uses a saved query instead. Saved queries are always faster than SQL statements because the query optimizer optimizes the query when it is saved instead of when it is run.

In addition, the SQL statement for frmSurveySlow's combo box includes the following ORDER``BY clause:

ORDER BY [LastName] & ", " & [FirstName]

In contrast, the SQL statement for the qryPersonComboBox query used as the row source for frmPersonPopup uses the following ORDER BY clause:

ORDER BY tblPeople.LastName, tblPeople.FirstName

Although these two ORDER BY clauses look similar, the first one sorts on an expression, whereas the second sorts on two indexed fields. It's always faster to sort on individual fields rather than expressions.

There are several other things to consider when looking for ways to speed up your forms. You may wish to try some or all of the following suggestions:

  • Preload and keep loaded forms (see Solution 8.1).
  • Ensure that fields used to sort or filter rows are indexed in the underlying tables (see Solution 8.4 for more on indexing and Rushmore).
  • Use referential integrity throughout your database. Besides the obvious improvements to the quality of your data when you create enforced relationships, Access creates hidden foreign key indexes that improve the performance of queries, forms, and reports based on the joined tables.
  • Create simpler forms with less color, fewer graphics and fewer fonts.
  • Limit the number of records in the form's recordset (see Solution 8.6).
  • Watch out for Tab controls with many pages and subforms on each page. Loading all those subforms will slow the opening of your form. One alternative is to load the subforms on a Tab control page only when that page is selected. You can do this by using the Change event of the Tab control to check the Value of the control. This tells you the PageIndex of the selected page. You can set the SourceControl property of your subforms only when the page they appear on is selected; you can't set it in design view.