Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 209113 - Last Review: July 27, 2004 - Revision: 2.1
Tips for improving subform performance in Access 2000
This article was previously published under Q209113
For a Microsoft Access 97 version of this article,
see
112747Â
(http://kbalertz.com/Feedback.aspx?kbNumber=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).
This article lists several things that you can do to
improve the speed and performance of your subforms.
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.
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.
APPLIES TO
- Microsoft Access 2000 Standard Edition
| kbhowto kbinfo kbperformance kbdatabase kbdesign kbusage KB209113 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate