ACC2000: Manipulating Objects with DAO May Cause Database Bloat

MORE INFORMATION

Steps to Reproduce Problem

1. Open the sample database Northwind.mdb.
2. On the Tools menu, point to Database Utilities, and then click Compact And Repair Database.
3. Press CTRL+G to open the Immediate window.
4. Type the following in the Immediate window, and then press ENTER:

   ?FileLen(CurrentDb.Name)
   						

   This function returns the file size in bytes of the currently opened database (Northwind.mdb). Note of the current size.
5. Create a module and type the following line in the Declarations section if it is not already there:

   Option Explicit
   					

6. Type the following procedure:

   Sub CreateTables()
      Dim db As Database
      Dim t As TableDef
      Dim f As Field
      Dim i As Integer, j As Integer
   
      Set db = CurrentDb()
      For i = 1 To 20
         Set t = db.CreateTableDef("Table" & i)
         For j = 1 To 200
            Set f = t.CreateField("Field" & j)
            f.Type = dbText
            f.size = 50
            t.Fields.Append f
         Next
         db.TableDefs.Append t
      Next
      Application.RefreshDatabaseWindow
   End Sub
   					

7. On the Debug menu, click Compile Northwind. When Microsoft Access prompts you for the name of the module, click OK to accept the default name.
8. To run this procedure, type the following line in the Immediate window, and then press ENTER. It may take several minutes for this procedure to run.

   CreateTables
   						

   Note that twenty tables are added to the database, each with two hundred fields.
9. Type the following line in the Immediate window, and then press ENTER:

   ?FileLen(CurrentDb.Name)
   						

   Note that the file size of Northwind.mdb is now reported to be more than 52 megabytes.
10. On the Task Bar click Microsoft Access to get back to the Database window.
11. On the Tools menu, point to Database Utilities, and then click Compact and Repair Database.
12. Press CTRL+G to open the Immediate window.
13. Type the following in the Immediate window, and then press ENTER:

    ?FileLen(CurrentDb.Name)
    						

    Note that the file size of Northwind.mdb after compacting is now back to the size you saw in step 4.