All of the tips so far have been aimed at specific coding priorities. Some increased real execution speed, or reduced network traffic, and others reduced the memory footprint of the database application.
In some cases, a single optimization may bring many benefits. For example, changing a variable's data type from variant to integer will reduce memory demands and may increase execution speed.
However, in other cases, an optimization may have an antagonistic effect. It may bring a benefit and incur a cost. For example, loading forms and hiding them will increase the apparent execution speed of your application, but it will also increase your application's memory footprint. In that situation you must decide what your priorities are and act accordingly .
The final section of this chapter concentrates on the things that you can do which will always benefit your application - irrespective of your coding priorities. These include:
Compacting the database
Compiling all modules
Opening databases exclusively
Over a period of time you may find that the performance of your database slowly degenerates. This may be because the database has become fragmented .
Fragmentation occurs when objects are deleted from a database, but the space used by those objects isn't reclaimed. The database becomes like a Swiss cheese - full of little holes. As pretty a simile as that may be, it also means that your database slows down. It's not damaged in any way, but performance suffers. This is because it is physically slower to read non-contiguous (fragmented) data from a disk than it is to read contiguous data.
Compacting a database removes any unused space (the holes in the cheese!) and makes all the data pages in the database contiguous. This has two benefits:
Database performance improves
The size of the database file is reduced
As well as allowing you to compact a database from the menu bar - just select Compact and Repair Database from the Database Utilities submenu on the Tools menu - Access 2002 now provides you with the ability to compact databases automatically when they are closed. In order to turn on this feature, simply check the Compact on Close checkbox on the General tab of the Access Tools/Options dialog box.
You can also compact a database from VBA, using the CompactDatabase method of the DBEngine object:
For optimal performance, you should occasionally use a disk defragmentation program (such as the Disk Defragmenter supplied with Windows 98 or later) before compacting your database.
You have been working feverishly all weekend to get that database application finished for Monday's demonstration to the board. You tested the application last night - making sure you tested it in a production environment - and it was really zippy. There's an hour to go and you think you might as well run that little library routine of yours to add fancy headers to the procedures. It only takes a couple of minutes to run and you've done it so often you know that it's bug free.
The time comes, the board members sit down and you hit the icon to start your application... and wait... and wait... and wait...
"Whaaaaat!" you scream, inwardly, of course. "What's happened to my speedy app???" It's suddenly performing like a three-legged dog... in a coma. Looks like you forgot to recompile your application!
When you make any changes to code in a standard code module or a class module (including form and report modules), the module has to be recompiled before it can be run. To compile the code in all the modules in your database, choose Compile < ProjectName > from the Debug menu in the VBA IDE.
If you don't explicitly compile your code in this manner, VBA compiles your code at run time. This can cause a significant delay, especially if there is a lot of code in the module being compiled. This delay is reduced, however, if you have checked the Compile On Demand box, and the Background compile checkbox on the General page of the Tools/Options... dialog in the VBA IDE (we discussed these options in Chapter 12). In this case, VBA only compiles the parts of the code that are called by the procedure that is executing - the call tree - rather than all of it, but uses idle time during run time to finish compiling the project in the background. So there is less delay. However, to be safe, you should always compile all your code before delivery. After all, compilation will also detect compile-time errors such as a For... statement without a corresponding Next statement.
You can increase performance further still by saving your database as an MDE file. We looked at MDE files - and the whole area of compilation - in more detail in Chapter 18.
If you are the only person who will be using the database at any one time, you should open the database exclusively . This means that your application will perform better because Access will not have to spend time monitoring whether other users want to lock records. You can ensure that databases are opened exclusively by default by selecting Exclusive as the Default Open Mode on the Advanced page of the Access Tools/Options... dialog.
If you use a command line to start your application, you can use the /Excl switch to achieve the same result.
c:\access\msaccess.exe c:\abwrox\code\wrox.mdb /Excl
If you are opening the database in VBA, set the Exclusive argument to True when using the OpenDatabase method:
Set db = DBEngine(0).OpenDatabase("c:\abwrox\code\wrox.mdb", True)