Chapter 8. Optimization

One unavoidable fact of application design is that your application never runs as fast as you'd like it to. Unless you and your users are equipped with the latest and most powerful workstations with huge amounts of memory, performance will be less than ideal. Still, there are many techniques you can use to optimize your application, few of which are easily found in the Access documentation. Although your Access application may never run like that lean and mean dBASE II application you created 15 years ago, you certainly can make it run at an acceptable speed.

This chapter covers several optimizations that enable you to load forms faster, add and change data faster, and speed up your Visual Basic for Applications (VBA) code, for example. It also covers the optimization of queries, as well as multiuser and client/server optimization techniques. In addition, this chapter describes testing techniques that will help you gauge the speed gains of your optimizations.

Several of the examples in this chapter take advantage of the DAO type library, rather than the default ADO library used by Access 2002 and Access 2003. Even though it's less "modern," DAO provides greater functionality, and generally better performance. In addition, using DAO makes it possible for these demonstrations to work in earlier versions of Access. If you want to try these techniques in your own applications, make sure you add the DAO reference to your project using the Tools References menu item from within VBA—it won't be added by default.