|
Free Resources from FMS |
|
|
|
|
"Thank you! Thank you! I just finished reading this document, which was part of a link in the recent Buzz newsletter. I have printed it for others to read, especially those skeptical on the powers of Access and its capabilities." - Darren D. |
|
|
|
|
Microsoft
Access Resource Center
Automatically Compact and Repair Databases
Provided by: Molly Pell, Technical Project Manager
Microsoft Access/Jet databases require routine maintenance to ensure reliability, integrity, and efficiency. Compacting and repairing an Access/Jet database does the following:
-
Consolidates and organizes data pages, recovering space used by deleted or renamed objects.
-
Updates table and index statistics so the query optimizer can choose the most efficient query plan.
-
Physically re-orders the rows in the table in the order of the chosen clustered index on the table.
-
Resets the next available number for AutoNumber (or "counter") fields so the next available number is one higher than the current highest value in the table.
-
Repairs the database if structural inconsistencies are found.
If you are diligent enough to remember this maintenance task, you can manually compact and repair a database by opening it in Microsoft Access, and selecting the Tools | Database Utilities | Compact and Repair Database menu item. However you can save yourself some time and effort by configuring your database to compact and repair itself every time you close the database:
-
Open the database that you want to compact on close.
-
Select the Tools | Options menu option to open the Access Options form.
-
On the General tab, check the “Compact on Close” check box.
Further Automating Maintenance
Turning on “Compact on Close” certainly helps ensure that these important maintenance tasks are performed regularly, but there are certain drawbacks:
-
The Compact on Close option can significantly increase the time that it takes to close the database, and can use considerable system resources.
-
Databases are only compacted and repaired when you open and close them interactively. Backend/linked databases that are not open interactively must be compacted and repaired some other way.
-
Compacting and repairing requires exclusive access to the database. For multi-user databases, you must ensure that no users have the database open prior to compacting and repairing.
You can increase the ease and reliability of database maintenance by
implementing an automated maintenance schedule using a product such as
Total Visual Agent from FMS. Using Total Visual Agent, you can schedule
maintenance tasks such as compacting and repairing to run after hours
when the task will not interfere with your work, and users are less
likely to have the database open.
