Focus on using common objects to speed Access 2007 databases

Many times we need to speed up access databases. This article describes how the use of data can make this demand more interesting. This article also explores Access 2007 ACCDB a feature called TempVars. But first let's discuss some user scenarios.

Scenario One: An experienced Excel technician built a database with a large number of associated data tables. He believes he needs to upgrade to SQL Server to speed up the database. This database is only 20MB in size, but some forms are very slow, with many label controls and hundreds of discrete fields in many child forms. In addition, users who use this database need to rummage through records every time to find the records they are looking for. Just add a Find record box to make the lookup data much faster.

Scenario Two: A complex database has been developed for six years, and now developers have left the company. In the last three years they did nothing but compress and repair the database, but the database was poorly performing. In this database, some tables take two minutes to close after pressing the Close button. Also, some reports need to run for one hours.

Scenario Three: A large database has been converted to use SQL Server as a background; it has 250 tables and 80 tables. At peak times its performance is very slow.

Good. So what can you do? First we can assume that a slow database is a complex database. So if you want to speed up each object, then it takes a long time. This means that you can use only a portion of the database, and it's best to confirm which objects in the database are worth using as described below.

Visit the primary user to find out which forms and reports are slow and make a list. From this list, find the five most commonly used slow objects and use them. This will tell you how much difference you can make and how long it will take.

So let's see how we can find out how often a form or a report is used, and then you can focus on how to make these common objects faster.

Tip: Remember, this database may need to be redesigned, so don't invest too much and change a lot of limited details to speed up the database that really needs to be redesigned.

Logging when a form or report is used in Access 2007

Here is a log-recording code solution. To test with a good Access 2007 database, download the task template from the Access template (Figure 1) suite and add the code "New Age" logging solution.

Figure 1–access 2007 database contains some of the tasks used in this article