Optimizing an Access Application
Many pages have been written about optimizing an Access application. In the space available here, I can only summarize what I've personally found to be most useful. I encourage you to look in other books, magazine articles, and Web sites for more detailed information and for some ideas in addition to those mentioned here.
NOTE
These are two books that contain a lot of useful information about optimizing Access applications: Microsoft Access 2000 Development Unleashed, published by Sams Publishing (particularly Chapter 14, "Application Optimization"), and Access 2000 Developer's Black Book, published by Coriolis (particularly Chapter 21, "Completing the Implementation").
Optimizing the Hardware
You may well find the applications that you create work reasonably well on your computer but are much slower on other computers. If that's the case, the first place to look is at the hardware configuration.
Windows and Office applications, particularly Access, require a lot of RAM in order to run efficiently. Although Microsoft specifies a minimum of 12MB of RAM to run Access under Windows 98 (16MB to run under Windows NT), these figures are bare minimums. To get good performance out of Access, a realistic minimum amount of RAM is 32MB (64MB is even better).
When you're developing an Access application, you probably have only Access running. In contrast, people who use your application might well have other programs running at the same time, each of which requires additional RAM. If people complain that your Access application runs slowly, make sure that they have enough RAM. Also discourage users from having other applications in addition to Access running at the same time, unless those other applications are absolutely necessary.
NOTE
Applications reserve memory for their own use while they're running. That memory should be released when the application closes. For various reasons, some memory reserved by applications isn't always properly released. As a result, a computer can gradually lose access to some of its memory—this is known as memory leakage. To recover memory that's lost in this way, you should shut down and restart Windows on a regular basis, such as once a day.
After making sure that users have sufficient memory, the next thing to consider is processor power and speed. Although the most advanced and fastest processor isn't required, naturally the speed of an application depends on the power and speed of the processor.
Then there's the question of hard disk performance. Access makes intensive use of the hard disk, so it's important to have a high-performance disk that's properly maintained. For efficient use of Access, the hard disk should be regularly defragmented so that it can access data efficiently. Don't forget that the hard disk is used as virtual memory (an extension to the physical RAM) as well as for storing all the data in your tables.
Whenever you add data to your tables or change existing data, the space occupied by the tables on your hard disk increases. Also, when you delete data, the space occupied by that data isn't necessarily recovered. For that reason, you should make sure that users regularly compact the Access application.
TIP
As the Readme file that accompanies Access points out, disk compression degrades the performance of Access. If you need good performance from Access, don't compress your hard drive.