Quick Find: Search for:
Free Resources
from FMS
 

Demos

Email Newsletter

Technical Papers

Tips and Techniques

Links

Book Recommendations

View all FMS products for Microsoft AccessMicrosoft Access Resource Center

 

 

"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.
 
  

 

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:

  1. Open the database that you want to compact on close.

  2. Select the Tools | Options menu option to open the Access Options form.

  3. 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.
 

Return to the tips page.

Questions  l   Web questions: Webmaster   l   Copyright © 2008 FMS, Inc.

Celebrating 21 Years of Software Excellence