Subject: INFO: Another change in Access 2000: Jet Compact vs. Access Compact
(Originally posted 1/24/00)
Prior to Access 2000, compacting a database was a very straightforward thing to be doing, and it was the same
whether you did it via DAO's CompactDatabase method, the Access menu option, the JetComp utility, or even the
TSI Synchronizer's Hidden CompactDatabase method. Jet would
reclaim all the space marked as deleted, rewrite all the data in the tables in primary key order, decompile all
the queries, etc. Since Access used individual rows in Jet tables to store information on it's objects, even the
Access stuff would be handled properly. Most importantly, all of the compact methods were equal in terms of what
they do to the database.
But this all changed in Access 2000.
The VBA project and all of the "Access-specific" items like forms, reports, macros, and modules have now been
moved into a single row in a table called MSysAccessObjects. Over time, if you are adding, changing, and/or
deleting Access objects, this huge binary chunk can become fragmented. However, Jet's Compact operation will not
do anything to the data in this table since it is just a single row of data. Therefore, Access made a change to
its Compact code from the menus that would defragment this information. This change even works with ADPs (Access
Data Projects) as well. Problem solved, right?
Not entirely! If you are trying to compact a database programatically, then all of the programmatic methods
mentioned above will not handle the information in the MSysAccessObjects table. So, what can you do? You can use
one of the following four methods to do the job:
The second option gives you an interesting workaround for a problem in the recently released Total Visual Agent
2000 from FMS, Inc.. It seems that for various reasons, its
automatic scheduled compact functionality uses the DAO CompactDatabase method internally, which as I mentioned
will not help with the Access project. However, since Jet removed the separate "Repair" method from the product
but Access did not want to confuse users by removing the /Repair command-line option, they simply made the
/Repair flag signal a Compact. Luckily, the FMS tool does handle its automated repair functionality through the
Access command line flag, which means that if you use Total Visual Agent 2000, scheduling repairs instead of
compacts will be an excellent way to keep your Access databases compacted properly.
- The Access 2000 "Compact and Repair Database" menu item
- Either the /Compact or /Repair command line flags for msaccess.exe
- The TSI SOON Database Add-In from the TSI Utilities
- The "Compact on Close" functionality added to Access 2000
Maybe future versions of Access will have their own "AccessCompact" method? In the meantime, you can use one
of the above techniques to compact your databases properly!
Update 28 Dec 2002: Access 2002 did the above, and added a CompactRepair method. Awesome!
Problems with this site? Please contact the
with your comments, questions, or suggestions.