It’s hard to work in an Access database without encountering forms. They’re used for everything — sharing general information, controlling workflow, and of course, browsing and updating data. It’s vital that forms perform well or users will complain. These five tips are easy to implement and should improve slow-performing forms. Better yet, make them part of your best practices and avoid the problems altogether. (Note: Although these are Access tips, they’re not all exclusive to Access.)
1: Limit the data
You hear it so often, it’s almost a mantra: Limit the data you retrieve. This practice follows through with forms. Limit the data retrieved by the form’s underlying query or procedure. Retrieve only the fields you need. Then, populate controls to display only the fields necessary. In addition, use criteria to limit the number of records the form must accommodate. You’ll seldom need to display all the records from a given data source. The less data you retrieve, the quicker the form will populate.
2: Avoid working with linked data
Querying linked data usually slows down everything, including form response. If you don’t need live data, save the linked resource locally and query the local table. Later, you can update the linked data if necessary. Your forms will populate local data much quicker than via a linked table.
3: Convert subforms
Access makes subforms easy to create and use — wizards do almost all the work. It’s little wonder so many forms use them. Unfortunately, subforms can slow down form performance. When possible, use list or combo controls instead of subforms. These controls load quicker than a subform. Remember to display only the fields necessary (see #1).
If you do use subforms, be sure to index the linking field(s). Also index subform fields used for filtering and sorting, unless the values in those fields are mostly repetitive.
4: Don’t over-index
Indexing can improve performance, but it can also slow things down — especially if the form is used for data entry. Every time you update a table, Jet refers to the index and sorts it accordingly. That takes time. Index a field when it meets all of the following criteria:
- The field is a Text, Number, Currency, or Date/Time field.
- You plan to search or sort records frequently.
- The field contains mostly different values.
If you’re not sure, run the Performance Analyzer. If it doesn’t recommend an index, you probably don’t need one. (Run the Performance Analyzer anyway for performance-improving suggestions.)
5: Create the impression of speed
Even the most optimized form can be a bit slow to open and display its first record. When this is the case, open the form as hidden when you launch the database. Then, when needed, change its Hidden property to False. It’ll appear instantaneously. A slowly loading form won’t annoy the user because the user will never see it load.
To open a form that’s hidden, use the following command:
DoCmd.OpenForm formname, acNormal, , , , acHidden
To unhide the form, use the following code:
Set frm = Forms!formname frm.Visible = True