You might have seen that your Access database file keeps on growing in size, even though you have not been putting in many new data. This is caused by the way how a database is structured. For all the database software that I used before, the database software leaves an unused section every time you delete or update some data. It means that the data are deleted but the sections that originally stored such data are still there in the database. Such sections become unoccupied and serve no purposes.
When you have too many such unoccupied sections within your database file, your database file will become fragmented and the performance will slow down. In addition, the size of the database file will keep on growing.
Benefits of Compacting a database file
Running the Compact and Repair Database utility within Microsoft Access improves the performance of the database. This utility makes a copy of the database file and, if it is fragmented, rearranges how the database file is stored on disk. When completed, the compacted database has reclaimed wasted space, and is usually smaller than the original. By compacting the database frequently, optimal performance of the database application is ensured, and page corruptions due to hardware problems, power failures or surges, and so on are resolved.
If a primary key exists in the table, compacting restores table records into their primary key order. This provides the equivalent of Non-maintained Clustered Indexes, and makes the read-ahead capabilities of the Microsoft Jet database engine much more efficient.
Compacting also updates the table statistics within the database that are used as Jet optimizes queries. These statistics can become outdated as data is added, manipulated, and deleted from the various tables. Query speed will be enhanced significantly, because they are now working with data that has been rewritten to the tables in contiguous pages. Scanning sequential pages is much faster than scanning fragmented pages. Queries are forced to recompile/optimize after each database compaction.
Steps to compacting a database
Step 1: Click the Office button and then choose “Access Options”
Step 2: Click the “Current Database” and mark the “Compact on Close” options.
During compaction, you can use the original name for the compacted database file, or you can use a different name to create a separate file. If you use the same name and the database is compacted successfully, Microsoft Access automatically replaces the original file with the compacted version.
Now every time when you close your Access database file, the file will be compacted automatically.
Notes
For the Compact operation to succeed, you must have enough storage space on your hard disk for both the original and the compacted database.
You cannot compact an open database. In a multiuser environment, the compact operation is not successful if another user has the database open.
