Microsoft small business knowledge base

Article ID: 273016 - Last Review: October 21, 2013 - Revision: 2.0

This article was previously published under Q273016
This article has been archived. It is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

On This Page


In the "Set Options from Visual Basic" Help topic in the Microsoft Access 2000 Visual Basic Editor, under the "General Tab" information, there is a feature listed that is called "Compact when database will shrink by this percentage or more." The string argument of "Auto Compact Percentage" can be changed by using the SetOption method in Visual Basic for Applications. This feature should compact an Access database (.mdb) or an Access project (.adp) only when the file would shrink by the specified percentage or more.

The Help file is incorrect. The Auto Compact Percentage feature can be set by using SetOption and retrieved by using GetOption; however, the value is ignored by Access 2000. The compact is completed, regardless of the percentage value.


Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.


Steps to Reproduce the Behavior

  1. Create a new blank database and name it CheckCompact.mdb.
  2. Import all the objects, except for data access pages, from the sample database Northwind.mdb.
  3. On the File menu, click Database Properties. Click the General tab, and look at the size of CheckCompact.mdb.
  4. Delete all the objects, except the tables and the queries.
  5. Close CheckCompact.mdb.
  6. Create a copy of CheckCompact.mdb and name it CheckCompact2.mdb.
  7. Open the CheckCompact2.mdb database.
  8. On the Tools menu, point to Database Utilities, and then click Compact and Repair Database. Look at the size of the database. It should shrink by approximately 35%.
  9. Open CheckCompact.mdb.
  10. Create a new module, and then add the following code:
    Sub TestCompact()
        Application.SetOption "Auto Compact", true
        Application.SetOption "Auto Compact Percentage", "80%" 
    End Sub
    NOTE: This code can be tested with any percentage greater than what you found after completing step 8.
  11. In the Immediate window, type the following line, and then press ENTER:
  12. Close CheckCompact.mdb.
  13. Look at the size of CheckCompact.mdb in Windows Explorer.

    NOTE: According to the Help file, the size of CheckCompact.mdb should be the same because the space to be recovered is less than 80%. However, the size of the database is smaller because the percentage was ignored and compact has been run.

  • Microsoft Access 2000 Standard Edition
kbnosurvey kbarchive kbbug kbpending KB273016
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support