A Blue Claw Database Design Template:
Customer Relationship Management (CRM) Template
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:
|Form performance is an important
component of creating a user friendly ... For additional resources
relating to Access form performance improvement and ...
|Also, when performance is an issue,
Access visual basic code will be much faster than macros. Summary of our
Microsoft Access Visual Basic programming ...
|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 ...
|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 ...