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 Microsoft Access 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.
Return to the tips page
|
|
