Optimizing Microsoft Access: How to Reduce the Size of an MS Access Database
Microsoft Access
Access databases will eventually bloat and grow. This growth makes Access slow down when opening and performing basic queries. Because these databases keep getting larger and larger, people using MS Access should perform regular maintenance on them. Microsoft Access should be used on a local area network only and should not be used on a WAN (Wide Area Network). Remote users can be given access through Terminal Services.
Compating the Database
In order to compact the database for optimal performance, users should periodically go to the database utilities under the tools menu (Access 2000 / 2003 .mdb files). Access 2007’s compact option is found under the Office 2007 Logo, Access Options, Compact on Close. These options can be selected during the development or after a database has been developed.
Databases that have been used over a period of time can be reduced 50 – 70% in size after being compacted. This optimization can improve performance from 10 - 60% depending on the amount of space saved and the design of the database.
Compacting MS Access 2003
Compacting MS Access 2007
Problems and Issues
Some problems may prevent you from compacting your dataset. Because Microsoft access can be shared (and commonly is), this procedure will fail if someone else is in the database. Compacting also relies on security settings on your network and network shares. Typical users may not have permission to compact a database. During the compaction procedure, your hard drive will need extra space (up to three times the database size) in order to complete.
Prior to any maintenance procedures, a complete backup of the database needs to be performed. Although you may have a copy of the database, it is important to get a full backup just before you compact it. You should also make a point in communicating with other users before this process is run.
Conclusion
In order to optimize the performance of any database, you should perform regular maintenance to insure optimal performance. Failing to do so will result in a bloated database.
Microsoft Access theoretically can support 255 users. This maximum user ’number’ is based on the use, complexity, and size of the database. If you belong to a group that requires additional users and has the ’need for speed’, Microsoft SQL then becomes the database of choice.
