<%dim crumb,crumb1 crumb="Speed Up Forms" crumb1="Make Faster Access Databases" %> Speed Up Form Opening in Microsoft Access
Home  Fees/Services  Access Examples  Tutorials  Access Download  Articles  Search  Contact  Privacy  Links

Access Form Programming Tutorial
<% response.write crumb1 %>

Home > Microsoft Access Tutorials > Access Forms Tutorial <% response.write ">" & crumb %>
<% Gnum="9096031968" Ynum="7841" dim Page_Type Page_Type="Prog" response.write "" %>

Form Subtotal Fields
Master/Detail Forms
NotInList Event
Speed Up Forms
Unbound Form
Change Textbox Height
Get Website Data
Access Audit Trail

Form Query Parameter

Speed Up Access Forms

Are your forms slow?. Give us a call and we will solve the problem or there is no charge! $100 1/2 hour.

Slow opening MS Access forms can annoy the user.  Program activation can sometimes take more than a minute when using a tab control form interface. We have developed a simple method that often speeds up program activation and tab control 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:

tab control example forms

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:

tab control programming examples

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:

form programming tutorials

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. 

speed up access forms

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:

programming Access forms with Tab Control

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 ...

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 ...

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 ...

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 ...



Microsoft Office Forms:
 MS Access 2003
 Access 2007
 Access 2010
 Access 2013

Contact Information

<% Response.write "Copyright 2000-" & year(now) & " Blue Claw Database Design" %>