Part IV: Professional Database Development
856
Note
Although databases should be compacted regularly, that does not mean they should be compacted every time
they’re used. I advise against using the Compact on Close option.
All the preceding methods are excellent (and necessary) ways to help keep your applications run-
ning at their optimum performance level, but these aren’t the only tasks that you can perform to
increase the absolute speed of your application. Almost every area of development, from forms to
modules, can be optimized to give your application maximum absolute speed.Tuning your system
One important aspect of performance has nothing to do with the actual application design — that
is, the computer on which the application is running. Even though it’s impossible to account for all
the various configurations your clients may have, you can do some things for your computer and
recommend that end users do them for theirs:l (^) Equip the computer with as much memory as possible. This step often becomes an
issue related to the cost of purchasing and installing the computer memory. However, as
memory prices continue to decrease, one of the most effective methods of increasing the
speed of Access applications is to add additional memory to the user’s computer.
l (^) Close all applications that aren’t being used. Windows makes it very handy to keep as
many applications loaded as you want — on the odd chance that you may need to use one
of them. Although Windows XP, Windows Vista, and Windows 7 are very good at han-
dling memory for multiple applications, each application still uses computer resources.
l (^) Make sure that your Windows swap file is on a fast drive with plenty of free space. If
possible, you should also set the minimum hard drive space available for virtual memory
to at least twice the physical RAM installed and make it a permanent swap file.
l Defragment the hard drive often. Defragmenting a hard drive allows data to be retrieved
in larger sections, thus causing fewer reads and less repositioning of the read heads.
Getting the most from your tables
The preceding sections documented many technical issues that should be reviewed to improve
application speed, but sometimes it’s advantageous to get back to the basics when designing your
applications. Tools like Access enable novices to create relational databases quickly and easily, but
they don’t teach good database design techniques in the process. (An exception to this statement is
the Table Analyzer Wizard. Click the ribbon’s Database Tools tab, then click the Analyze Table
command in the Analyze group to start the Table Analyzer Wizard.)
Caution
Even though the Table Analyzer Wizard offers suggestions that are often helpful in learning good design tech-
nique, its recommendations should never be taken as gospel. The Table Analyzer has proven to be wrong on
many occasions.