"Total
Visual Agent is a real find, a rare combination of ease of use, and bang
for the buck. In the end, no other competitor even comes close."
Thomas
Wagner, Product Review in MS Office and VBA Developer
|
|
Automatically Compact and Repair Microsoft Access Databases with Compact On
Close
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 Compact and Repair command from the menu (see
Microsoft Access
Compact and Repair by Access Version). However, you can save yourself
some time and effort by configuring your database to compact and repair
itself every time you close the database.
Compact on Close
Open the database you want to compact on close.
Access 2007 and 2010
- From the Office button, select Access Options
- Select the Current Database item on the left border
- Check the "Compact on Close" box:
Access 2003 or Earlier
-
Select the Tools | Options menu option to open the Access Options form
-
On the General tab, check the "Compact on Close" box:
Issues and Limitations of Compact on Close
Turning on "Compact on Close" certainly helps ensure that
these important maintenance tasks are performed regularly, but there are
limitations:
-
The Compact on Close option can significantly increase the time that it
takes to close the database, and can use considerable system resources
which could delay the user from getting other work done.
-
Databases are only compacted and repaired when you open
and close them interactively. Backend/linked databases that are not opened
interactively must be compacted and repaired in another 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.
Blog on this topic:
Automatically Compact and Repair Microsoft Access
Databases with Compact On Close
Additional Resources and Technical Papers
Microsoft Access Database Administrator Products
Schedule and Automate Database Compacts
Total Visual Agent manages an unlimited number of databases across your network and
schedule database chores in the middle of the night. Perform
compact, backups, data extracts, running macros, command lines, and
other tasks. Schedule events hourly, daily, weekly, monthly or just
one time. Complete audit trail with email notification if anything
goes wrong. Free Demo
|
Monitor Your Database in Real-Time
Total Access Admin lets you monitor
Access databases in real-time to see who's currently in it and when
they leave. It flags users who disconnect in a suspect manner which
may indicate a source of database corruption. It can also perform
tasks after everyone has exited such as compact.
Free Demo
|
|
|
