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 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 page.
• The "Compact on Close" functionality added to Access 2000

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.

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 webmaster@trigeminal.com
with your comments, questions, or suggestions.