Tips for Creating Faster Performing Forms

This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

Aa140070.ima-logo(en-us,office.10).gifACCESS 95, 97, 2000

Tips for Creating Faster Performing Forms

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.by Susan Sales Harkins

Speed is an issue in mostly every application, and forms can be terminally slow to load or update if you're not careful. In this article, we'll discuss a few tips that should improve your form's general performance. Fortunately, each tip is easy to implement and you can usually apply several of these tips to almost all of your forms.

Avoid busy forms

As a rule, keeping the number of controls in a form to a minimum improves that form's performance. We know it's tempting to display all of the fields in the underlying table or query--AutoForm can be a real time-saver when used properly--but every additional control slows things down just a bit. If you believe your form needs to display an unusually large amount of information, maybe you should reconsider the way you use your form. Quite often, you don't really need all of that data all of the time. Most likely, you need some of the data all of the time and other data only some of the time. When this is the case, simply move the noncritical data to a pop-up form that users can display only when needed.

While we're on the subject of controls, you should know that Memo and OLE fields significantly slow down a form. The best arrangement is the same given above--move these types of controls to pop-up forms that users can request as needed.

Specify read-only forms

Many forms are used just to view information--you never use them for data entry. When this is the case, be sure to set the form's AllowEdits, AllowAdditions, and AllowDeletions properties to No. This is especially helpful when you're working with a networked application since doing so eliminates record lock requirements. Another way to specify a read-only form is to use the VBA OpenForm statement in the form

DoCmd.OpenForm "formname", acNormal,,,acReadOnly

Localize lookup tables

If you're working with a networked or split database, store lookup tables in the local application or in the front-end, respectively. By lookup, we mean data that's not actually related to the application. For instance, your application may include a table of state abbreviations or ZIP codes for quicker data entry, but that data isn't part of the actual application. If users aren't updating these types of tables, storing them in the local application can improve a bound form's performance.

Simplify data sources

When possible, don't base a form on more than one table or query. When you do, Access creates pointers to each table or query and too many pointers can slow data retrieval. If you can't avoid a multitable arrangement and the form is slow, you might consider running a Make-Table query and basing your form on the results--if your form is for display purposes only. When basing your form on a query, it's best to use saved queries, instead of SQL statements. However, improvements to the Jet engine have significantly optimized SQL statements. Unless you're using an older version of Access, you probably won't notice any difference between saved queries and SQL statements.

By default, queries are dynaset recordset types, which allow changes. However, dynasets take longer to display. If you're using a query to fill a control or as the form's data source, and you don't need to update the underlying data, try changing the query's recordset type to Snapshot. To do so, open the query in Design view, right-click on the background, choose Properties from the resulting shortcut menu, and change the Recordset Type to Snapshot.

Create smart indexes

You should keep the number of indexes in a form's data source to a minimum. In fact, too many indexes or the wrong indexes can significantly slow down your form's performance. That's because Access updates the index each time you add or change a record--if you have more than one field indexed. Remember that Access automatically indexes a primary key field. If you consider indexing a second field, do so only if that field meets all of the following conditions:

  • The data type is Text, Number, Currency, or Date/Time.
  • You plan to search for data or sort records by this field frequently.
  • The field contains mostly different values.

If you're not sure whether to add an index, let the Performance Analyzer give you a hand. First, remove all the indexes from the table in question. Then, run the Performance Analyzer by choosing Tools/Analyze/Performance from the menu bar. If the Analyzer doesn't suggest the index, chances are you don't need it.

Avoid calculations on networks

If you're working with a networked database, don't perform calculations or take actions on the data in a retrieval query. As a general rule, Access performs these calculations or actions row by row as you retrieve the data from the server, which slows down the retrieval task. Instead, improve performance by retrieving the data and then performing calculations or other actions locally.

Improving exit time

Opening forms and retrieving data aren't the only speed issues where forms are concerned. Some forms close slowly. For instance, if you use the Filter By Form feature, Access saves the last filter when you close your form. That means your form takes a smidgen longer to unload. If you don't care to save the last filter, simply add the following line of code to your close or exit process:

DoCmd.Close acForm, "formname", acSaveNo

Since Access won't save the form, the form will close more quickly.

Conclusion

A wait of just a few seconds can become annoying when repeatedly opening forms. Even though most users have seen rapid speed increases in their PC systems over the last few years, you should do everything you can to optimize the performance of your applications. The tips we've shown you will help shave off some of the time your applications use to load and unload forms.

Copyright © 2000, ZD Inc. All rights reserved. ZD Journals and the ZD Journals logo are trademarks of ZD Inc. Reproduction in whole or in part in any form or medium without express written permission of ZD Inc. is prohibited. All other product names and logos are trademarks or registered trademarks of their respective owners.