Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. Tips for improving subform performance in Access 2000

Tips for improving subform performance in Access 2000

View products that this article applies to.

This article was previously published under Q209113
For a Microsoft Access 97 version of this article, see 112747� (http://support.microsoft.com/kb/112747/ ) .
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

For a Microsoft Access 97 version of this article, see 112747� (http://support.microsoft.com/kb/112747/ ) .

↑ Back to the top

Summary

This article lists several things that you can do to improve the speed and performance of your subforms.

↑ Back to the top

More information

To improve subform performance, try these tips:
  • If you can, base your subforms on queries rather than tables. Include only those fields from the record source that are absolutely necessary. Extra fields can decrease subform performance.
  • Index all the fields in the subform that are linked to the main form. Indexes help speed the search process to find the matching subform records.
  • Index any fields used for criteria (such as when a subform is based on a criteria query).
  • If you are linking on multiple fields, add a calculated field to the main form that concatenates the fields. Then create a calculated column in the subform's RecordSource property query with the same expression. For example, to link to the subform on an Employee ID field and an Order ID field, add a text box to the main form with the following properties:
    Name: EmployeeIDOrderID
    ControlSource: =[EmployeeID] & [OrderID]
    Next, add the following field to the query that the subform is based on:
    EmployeeIDOrderID: [Employee ID] & [Order ID]
    Finally, link the main form and the subform on the concatenated field rather than on the two individual fields. The subform properties may appear as follows:
    LinkChildFields: EmployeeIDOrderID
    LinkMasterFields: EmployeeIDOrderID
    Because Microsoft Access has to compare only one criteria to return the subform's recordset, the subform's performance should improve.
  • Set the subform's DefaultEditing property to Read-Only if the records in the subform are not going to be edited.
  • If your subform is a continuous form and contains combo boxes, explicitly justify the combo box in the subform's form Design view. This practice prevents Microsoft Access from determining the proper justification of the combo box values for each record, and therefore speeds the display of subform records that have combo boxes.

↑ Back to the top

References

For more information about general performance and indexingrecommendations, click Microsoft Access Help on the Help menu, type optimize performance in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

↑ Back to the top

Keywords: KB209113, kbusage, kbdesign, kbdatabase, kbperformance, kbinfo, kbhowto

↑ Back to the top

Article Info
Article ID : 209113
Revision : 3
Created on : 7/27/2004
Published on : 7/27/2004
Exists online : False
Views : 268