The purpose of these performance posts is to suggest ways you can try to improve the performance of your Access applications. Some of the tips are just a brain-dump from the author, others have been collated from various Microsoft, FMS and other third-party sources.
Note that some of the tips contradict each other. This is because an optimization in one part of your application may cause a bottleneck in another part. For example, to make a form with subforms open and scroll faster, you may add indexes to each of the fields used to link the form with the subform. This will indeed make the form faster, but the added indexes will make operations like adding and deleting records slower.
Also, there is no such thing as perfect optimization advice. Some of the tips given here may make things run faster on your specific system, some may introduce new performance problems. The point is that optimization is not an exact science. You should evaluate each tip as it applies to your specific application running on your specific set-up. To help in this regard, each tip is rated ■, ■■ or ■■■, with ■■■ being the most advantageous in the author’s experience.
Move startup code onto your startup form... ■
If you use a Startup form instead on an AutoExec macro, place the VBA code needed for that form in the form's module instead of a standard module. Since Access has to load your Startup form, it will automatically load that form's module, which is generally faster than loading a standard module. This technique gives your application the appearance that it is loading faster.
...but also minimize startup form code ■
Minimize the amount of code in your startup form. You may want to defer certain operations, such as opening data access objects in code, or checking objects to a later time. Again, this gives the impression that your application is loading faster.
Avoid using ActiveX controls on startup form ■
ActiveX controls can take longer to load than other controls and will subsequently slow down the load time of your application.
Avoid overlapping controls ■
It takes Access more time to render and draw controls that overlap each other than it does non-overlapping controls.
Think carefully about graphics sparingly ■
Use bitmap and other graphic objects sparingly (or not at all, if you ask me) as they can take more time to load and display than other controls. If you must use graphics, use the Image control instead of unbound object frames to display bitmaps. The Image Control is a faster and more efficient control type for graphic images. Also, if you have to use graphics use bitmaps with the lowest possible color depth. Consider black and white bitmaps where appropriate.
Access Help topic: Add a picture or object
Close unused forms ■
Close forms that aren't being used. Every form that is open consumes memory that could be used by other parts of your applications.
Open forms hidden ■
Consider opening your application's most commonly used forms when your application starts. Set their Visible properties to False, and then make the Visible as needed. This front-loads some performance hits to the application load event, making forms load faster when needed.
Use the DataEntry property of a form ■
If a form's record source contains a large number of records, and the form is primarily used to add new records, set the DataEntry property of the form to Yes. This precludes Access from having to retrieve existing records when the form loads.
Access Help topic: DataEntry property
Don't sort a form's recordset ■
Avoid sorting records in a form's underlying record source unless a particular presentation order is absolutely necessary for the form. This will make the form load faster.
Base forms on queries and minimize fields returned ■
Consider basing forms and subforms on queries rather than tables. By doing this, you can use the query to restrict the number of fields returned, making the form load faster.
Use lightweight forms ■■
Consider replacing VBA in a form's module with calls to standard modules, or with hyperlink objects. Then set the form's HasModule property to False. This turns the form into a 'lightweight' form, making it load faster.
Access Help topic: HasModule property
Index fields used to link subforms to a form ■
Try indexing all fields in the subform that are linked to the main form. Also index all fields in the subform that are used for criteria.
Set editing properties on subforms ■
Set the subform's AllowEdits, AllowAdditions, and AllowDeletions properties to No if the records in the subform aren't going to be edited. Or set the RecordsetType property of the subform to Snapshot.
Access Help topics: AllowEdits property, AllowAdditions property, AllowDeletions property, RecordsetType property
Reduce the number of fields in listbox and combobox rowsources ■
In the RowSource property of listbox and combobox controls, include only the fields that are necessary.
Index listbox and combobox fields ■
Index both the first field displayed in a combobox or listbox, and the bound field.
Set AutoExpand on comboboxes to No ■
Set the AutoExpand property of comboboxes to No if you don't need the "fill in as you type" feature.
Access Help topic: AutoExpand property
The first displayed field of an AutoExpand combobox should be text ■
In a combobox that has the AutoExpand property set to Yes, the first displayed field should be of type Text rather than Number data type. In order to find matches, Access needs to convert numeric values to text. If the data type is Text, this conversion can be skipped.
Optimize bound comboboxes ■
If the bound field in a lookup combobox is not the displayed field, don't use expressions for the bound field or the displayed field, don't use restrictions (the WHERE clause) in the rowsource, and use single-table row sources wherever possible.
Ensure linked data for combobox and listbox controls is local ■
If a the data that fills a listbox or combobox does not change often, and that data comes from a linked table, consider moving that data's table into the database. This can be a huge performance boost, especially if the linked table is located on a network drive.
Minimize the number of controls ■
Minimize the number of controls on your form. Loading controls is one of the biggest performance hits when loading a form.
Group controls on multiple pages ■
Consider grouping controls on multiple pages. When the form loads, prepare only the controls on the form's first page. Defer operations on other page's controls, such as setting the record source until the user moves to that page. This makes the form load faster.
Close forms that contain unbound OLE objects ■
Close forms that contain unbound OLE Objects when they are not in use. When you activate unbound OLE objects, the memory used in that operation is not released until the form is closed.
Convert subforms to listbox or combobox controls ■
Where possible, convert subforms to listbox or combobox controls. It is far quicker to load a control than it is to load an additional form as a subform.
Move form code to a standard module ■
You can reduce a form's load time by moving its code from the form module to a standard module. When the form loads, the form's module doesn't need to be loaded. Of course, the standard module needs to be loaded at some point, but once a standard module is loaded, it stays in memory until you close the database.
Access Help topic: About modules
Avoid unnecessary property assignments ■
Set only the properties that absolutely need to be set. Properties assignments can be relatively expensive in terms of performance. Review your form's startup code to ensure that you are not setting any form or control properties that don't need to be set.
Use the Requery method instead of the Requery action ■
Use the Requery method instead of the Requery action. The method is significantly faster than the action.
Access Help topic: Requery method
Give visual feedback ■
Give the user some visual feedback during long operations. Consider using status meters to display a task's progress. At a minimum, use the Hourglass cursor along with a status message. This won't make your application any faster, but it be perceived to be doing something - this counters perceived slowness!
Use native tab controls ■
If you have databases from old versions of Access that used OLE custom controls for tabbed dividers, replace them with the native tab control found in Access 97 and above - it loads and runs faster.
Access Help topic: Tab control
Keep forms lightweight with hyperlinks ■
Hyperlinks in label controls make it easy to open another Access object. Instead of placing command buttons on your forms to do common operations, investigate the possibility of using a label control with Hyperlink properties. This approach eliminates the need for a command button, and its associated event code.
Access Help topic: About hyperlinks
Split forms into multiple pages ■
Consider using multi-page forms, separated by the page-break character. This allows you to present only the controls needed, and can reduce form-load time. For example, if your form has 10 combobox controls that take along time to fill, split the form into multiple pages using the PageBreak control. Then, pick the 5 combobox controls the user is most likely to use and place them on the first page. Place the remaining controls on the second page. Load time for the form should be substantially reduced, especially if the queries filling those combo box controls are complex.
Access Help topic: Pagebreak control
Choose control types wisely ■
Different control types have different performance "weights" associated with them. When choosing controls for a form, keep the following relative weights in mind:
- Rectangle: weight of 1
- Line: 1
- Page Break: 1
- Label: 3
- Command Button: 5
- Option Button: 5
- Toggle Button: 5
- Checkbox: 5
- Tab Page: 5
- Textbox: 10
- Option Group: 10
- Image: 20
- Tab Control: 25
- Listbox: 40
- Combobox: 40
- Custom Control (OCX): 45
- SubForm: 50
- Object Frame: 50
- Bound Object Frame: 50
No comments:
Post a Comment