FMS Home FMS Software Development Team Blog FMS Facebook Page FMS Twitter
Jump: Search:

Total Visual Agent

Microsoft Access 2010/2013 Version
is Shipping!

With Windows Service!
Supports: All Access, Jet Engine, and Visual Basic versions


View all FMS products for Microsoft AccessAll Our Microsoft Access Products

Agent Info

 

"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

More Rave Reviews

Related Resources

 

 

 
Automated Task Scheduler for Microsoft Access Microsoft Access Database Compact SchedulerSchedule Microsoft Access database tasks for compact, backup, macro run, etc. Microsoft Access Database Insurance: Maintenance and Corruption Prevention

Compact and Repair Microsoft Access Databases on Your ScheduleAutomatically 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.

Microsoft AccessMicrosoft Access 2007, 2010 and 2013

  1. Open the Access Options form
    • For Access 2010 and 2013, from the File menu, select Options
    • For Access 2007, from the Office button, select Access Options
  2. Select the Current Database item on the left border
  3. Check the "Compact on Close" box:

Compact on Close in Microsoft Access 2013
Access Options in Microsoft Access 2013

Compact on Close in Microsoft Access 2007
Access Options in Microsoft Access 2007

Access 2003 or Earlier

  1. Select the Tools | Options menu option to open the Access Options form
  2. On the General tab, check the "Compact on Close" box:

Compact on Close in Microsoft Access 2003

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.

FMS Development Team Blog 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

Schedule and Automate Microsoft Access 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

See who's currently in your Microsoft Access database and prevent corruption

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

Feedback

Contact Us  l   Web questions: Webmaster   l   Copyright FMS, Inc., Vienna, Virginia
Celebrating our 28th Year of Software Excellence