DetailPage-MSS-KB

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

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.

APPLIES TO
  • Microsoft Access 2000 Standard Edition
Keywords: 
kbnosurvey kbarchive kbbug kbpending KB273016
Share
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