Database bloat is not stopped by compacting database with Access 2002 format (810415)


The information in this article applies to:

  • Microsoft Access 2002
This article applies only to a Microsoft Access database (.mdb).

Moderate: Requires basic macro, coding, and interoperability skills.

SYMPTOMS

If you have a database that is in Access 2002 format, database bloat occurs when you repeatedly add or remove large numbers of objects that contain code. You cannot stop the database bloat when you compact the database.

CAUSE

Records are added to the MSysAccessStorage system table when objects that contain code are created and the project is then compiled. Some of these records remain when the objects are deleted. These records are not removed when the database is later compacted.

WORKAROUND

To work around this problem, use either of the following methods:

Method 1

After you remove objects that contain code, create a new database and then import all the remaining objects from the original database to the new database.

Method 2

If possible, develop the database by using Access 2000 format. Then, when the database is ready to deploy, create a new Access 2002 format database and import all the objects from the Access 2000 format development database to the new Access 2002 database.

Note Whether you use Method 1 or Method 2, when objects are imported to a new database, the new database project must be compiled and then saved. If references other than the defaults are used, they must be modified in the new database.

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

205608 ACC2000: References Not Imported with Objects from Another Database

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 Problem

  1. Create a new Access 2002 format database and name it TestDb.mdb.
  2. On the Tools menu, click Options, and then click the View tab. Click the boxes next to System Objects and Hidden Objects. Click OK.
  3. Select Tables under the Objects bar in the database window.
  4. Open the MSysAccessStorage table.

    It contains 22 records.

    Close the table.
  5. On the Insert menu, click Module.
  6. Add the following code to the new module:
    Function Test()
     MsgBox "Hello World"
End Function
  7. On the Debug menu, click Compile TestDb.
  8. On the File menu, click Save TestDb.

    Accept the default module name.
  9. On the File menu, click Close and Return to Microsoft Access.
  10. Open the MSysAccessStorage table again. It now contains 31 records. Four of these records contain the following data in the Name field (# representing incrementing numbers):

    __SRP_#

  11. After you close the MSysAccessStorage table, click Modules under the Objects bar in the database window.
  12. Delete the new module that you created in Step 5.
  13. Open the MSysAccessStorage table again. Notice that the table now contains 25 records. Three of these records are the remaining __SRP_# records.
  14. After you close the MSysAccessStorage table, compact the database. On the Tools menu, point to Database Utilities and then click Compact and Repair Database.
  15. Open the MSysAccessStorage table again. Notice that the table still contains 25 records.
Modification Type:MinorLast Reviewed:8/3/2004
Keywords:kbtshoot kbRepair kbsetup kbprb KB810415 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.