Slow opening form?

The following was posted at http://ourworld.compuserve.com/homepages/attac-cg/AFormTip.htm#TOC

In all versions of Access the issue of form loading speed is critical to the sense of performance of the application. Here's some general rules for maximizing form loading speed, and minimizing load time:
1.) Minimize the number of controls and fields on your form. This may seem self evident, but its important. Use separate popup forms for various sections of data or for fields only used by certain individuals. If you must use a form with many fields, then create a front end record selector form to pick the record you want to view or edit and then open the form using a filter or Where clause to pick the proper record.

2.) Minimize the number of combo boxes and list boxes on the form, especially if they are based on another table; each combo or listbox will require Access to load one or more table pointers into memory for each table in the underlying queries. Convert combos and list boxes where possible to being based on value lists rather than queries. (See also the combo box topic on this page.)

3.) Don't display OLE fields or Memo fields when the form loads. Either place these fields on another page, or cover them with a box control, and in the OnClick event of the box control, set the box's visible property to false to display the memo or OLE field. Non-visible OLE and Memo fields aren't read from the db until they are made visible.

4.) Minimize the number of indexes in the underlying table, and be smart about indexes: Don't index both the Customer name and its ID number, since one is directly related to the other.

5.) If you split your database between data on the server and application on the local workstation and use look up tables for combo boxes for items such as state abbreviations, keep those tables (where the data doesn't change) in the local application database, rather then on the server. This will lower network traffic and look up will be faster from the local disk.

6.) For lookup forms such as Address books and forms used by individuals who only inquire about data rather than enter it, set the form to be read only, this is often overlooked and is a real boost, since it eliminates record lock requirements. (See the help file for the various versions of Access as to how to do this.)

7.) Move code behind the form to general database modules, and combine where feasible this code into one module so that all the code is loaded at the same time and ready for use. Better yet include in this code in a module has other code which is executed when the application starts (such as reattaching tables,) so that the form code is loaded when the db loads, rather than when the form loads. (If you make use of the Me object in your form module, when you move the code to a general module simply place a parameter in the Sub or Function call specifying a form object (e.g. Sub frmOrders_Order_AfterUpdate(Frm as Form), and replace Me in your code with "Frm". Call the function from the orders AfterUpdate event by specifying "Call frmOrders_Order_AfterUpdate(Me)").

8.) If you use a query for the form record source or for combo or list boxes, used saved queries rather than SQL statements for the record source. Saved Access queries are "pre-optimized" by the JET query optimizer, whereas SQL statements must be optimized at run time.

9.) See also the query section on query optimization.

10.) Dependent on your computer's amount of memory especially if it is less than 32 meg on a Windows 95 machine you may want to increase the default size of your virtual memory swap file. To do this in the computer's settings for virtual memory, (System properties, performance, virtual memory) increase your minimum virtual memory settings to at least 1.5-2 times the amount of base memory your computer has installed, if the minimum virtual memory setting is currently at zero. This will result in a large swap file being created at startup that is already available to handle Access' needs for virtual memory when it loads and opens forms. (Each time Windows needs more virtual memory it causes two disk writes once to mark the space and once to write to it when swapping. You can cut this time in half by having a large swapfile already available.)

This was posted by jfischer on Feb 7, 2002
thread181-207885

Here are a couple of things you could try in order to help the performance of Access 2000.

1) Access 2000 has a new feature called SubDataSheets that it sets up by default when you convert Access 97 MDBs. The SubDataSheets are set to (Auto) and should be set to (None). These SubDataSheets are included based on the Relationships you have defined. When you open a main table, Access 2000 then also opens all the sub tables related to it--big performance problem in some cases. To turn off the SubDataSheets, open a main table in Design mode, choose Properties, and set SubDataSheets to (None). You'll need to check each of your tables to see which ones Access 2000 has set to (Auto).

2) Access 2000 has a feature called "Name AutoCorrect" on the Tools, Options, General tab that can impact performance in some cases. Uncheck this option to see if it helps.

I have used these things and my form with 138 objects opens in 3 sec and not in 10 sec like it used to.