ACC2000: Auto Compact Percentage String in SetOption Does Not Function as Expected (273016)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q273016
Moderate: Requires basic macro, coding, and interoperability skills.

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

SYMPTOMS

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.

STATUS

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

MORE INFORMATION

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:
    TestCompact
  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.
Modification Type:MajorLast Reviewed:6/25/2004
Keywords:kbbug kbpending KB273016
©2004 Microsoft Corporation. All rights reserved.