Speed Up Access Forms

Slow opening MS Access forms can annoy the user.  Program activation can sometimes take more than a minute when using a tabbed interface. We have developed a simple method that often speeds up program activation and form opening by a factor of 10.  The method involves a simple technique - use local dummy tables as the record source for forms until the forms are viewed by the user.

Below is the setup of our tabbed interface:

There are more forms off to the right side.

The total count of all the form that get opened when the program starts up is about 20, counting the subforms.  This number of forms doesn't slow the database down much when all the tables are local to the front end.  However, when the database is split into a front end/back end arrangement the time for the program to start up increases dramatically.  For this database the total time exceeded one minute.  This slow startup was not satisfactory for the user.

Our solution was simple - create dummy tables for containing no records for most of the forms associated with each tab.  Below is a list of some of the tables in the database:

The Z_ tables are the dummy tables and you can see the real linked tables for most of them towards the top of the list - these begin with M_.

The image below shows the contents of one of the Z_ tables:

Note that the table contains a single dummy record.  We have found that this works best compared to having no records in the table.

The image below shows that the Record Source for the stabling form is set to the dummy table Z_Stabling. 

Next we need to show you how and when to switch to the real table.  We change the record source to the real table when the user clicks on the Stabling tab:

Note that we have defined an Event Procedure associated with the On Change event of the TabCtl0 object.

Below is the code for the On Change event procedure:

So, when the user clicks on the Stabling tab the code checks to see if the record source is set to the dummy record source and if so then changes the record source for the form to the real table 'L_Stabling'.

The result of using this technique speeded up the database activation from over a minute down to 8 seconds.

Go here to see more about Select Case statements.

We have many more examples of using tab controls in Access databases.

More performance improvement discussion and resources:
 

Access Form Examples

Form performance is an important component of creating a user friendly ... For additional resources relating to Access form performance improvement and ... www.blueclaw-db.com/access_form_programming/

Visual Basic Examples

Also, when performance is an issue, Access visual basic code will be much faster than macros. Summary of our Microsoft Access Visual Basic programming ... www.blueclaw-db.com/accessvisualbasic/

Table Design: Microsoft Access Relational Table Design Tutorial

Numeric index pointers to lookup lists is a way to increase the performance of tables with many data fields. Another way is the use of autonumber fields to ... www.blueclaw-db.com/tabledesignaccess/

Create Index in Microsoft Access

This is usually referred to as a pseudo index and can significantly improve the performance of slow ODBC queries by adding an index on field references in ... www.blueclaw-db.com/create_index_sql_ddl.htm