Compacting Microsoft Access Databases to Improve Performance
Microsoft Access is a widely popular local database. Access conveniently
stores all database objects within a single MDB file. The convenience of a
single file database results in an annoying problem however. Over time as
database objects are changed and deleted the database file becomes
fragmented. This fragmentation impacts performance. Therefore part of the
maintenance process of Access database files is to periodically compact the
databases which will defragment the database and provide optimal performance.
An added benefit of this compaction is that the resultant database file will
also usually take up less disk space.
The compaction process improves performance in two main ways. First any primary key indexes will be reordered in an optimal way. This makes the Access read-ahead features more efficient. Secondly, compaction updates all table statistics. Table statistics are used extensively during queries. When the table statistics are updated queries will be much faster.
Defragmenting the drive the Access database resides on can also improve performance. This is because a fragmented database file requires disk access over many different areas. More disc access means slower operations. When a database file is in a contiguous location on the hard disk there are few disk accesses required providing better performance.
It is important to remember to compact your database immediately after defragmenting your drive. This will make it more likely that the database file will be placed at the start of the open space on the drive. Then when the database file requires more space it can use the space immediately after it avoiding fragmentation.
In Microsoft Access databases can be compacted by selecting the Compact and Repair Database icon in the Database Tools tab.
CyberMatrix has a solution for scheduling Access databases repairing and compacting. This software is called AccCompact. Click here for more information on AccCompact.
Cimaware provides a utility called AccessFIX to not only repair Access table corruption but also recover deleted tables, queries and other database objects. Click this link for more information on AccessFIX.