PRB: 'For Each' with 'Delete' Doesn't Work for DAO Collections

• Standard, Professional, and Enterprise Editions of Microsoft Visual Basic, 16-bit and 32-bit, for Windows, version 4.0

SYMPTOMS

Using the For Each method to iterate through and delete objects from DAO collections fails to delete all of the objects in the collection.

CAUSE

The Jet Data Access Object (DAO) automatically compacts collections. This causes the indices of the objects within the collections to change dynamically. The For Each statement identifies all of the items in a collection that it will iterate through only at the initial entry into the loop.

RESOLUTION

Use the standard For...Next statement to iterate through DAO collections. Looping should start at the upper bound of the collection and step backwards through the collection. This allows the compaction to happen normally without affecting the loop. For example:

   For I = DB.TableDefs.Count - 1 To 0 Step - 1

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. Start a new project in Visual Basic. Form1 is created by default.

2. Add the following code to the Form1_Load procedure:

      Private Sub Form_Load()
         Dim DB As Database
         Dim MYdbSystemObject As Long
         Dim I
   
         MYdbSystemObject = &H80000000
         On Error Resume Next
         Kill "TestDB.MDB"
         On Error GoTo 0
         Set DB = DBEngine.Workspaces(0).CreateDatabase("TestDB.MDB",_
            dbLangGeneral)
         For I = 1 To 10
            Call AddTD(DB)
         Next
         Debug.Print DB.TableDefs.Count
         For Each TDObj In DB.TableDefs
            If (TDObj.Attributes And MYdbSystemObject) <> _
               MYdbSystemObject Then
            Debug.Print TDObj.Name ; " deleted"
            ' This test is to avoid the System tables supplied by Jet.
            ' These tables may not be delete from the collection.
            DB.TableDefs.Delete TDObj.Name
            End If
         Next
         Debug.Print DB.TableDefs.Count
      End Sub
   
   

3. Add the following code to the General Declarations section of Form1:

      Sub AddTD(DB As Database)
         Static I As Integer
   
         I = I + 1
         Dim TD As New TableDef
         Dim FD As New Field
         TD.Name = "Table" & Trim$(Str$(I))
         FD.Name = "Field" & Trim$(Str$(I))
         FD.Type = dbInteger
         TD.Fields.Append FD
         DB.TableDefs.Append TD
         Debug.Print "Added Table "; TD.Name
      End Sub
   
   

4. Start the program by choosing Start from the Run menu or by pressing the F5 key. The program lists the tables that it adds, but it only deletes the odd-numbered tables.

5. To get the code to work correctly, modify the For Each loop. Replace the For Each statement with the following two statements:

   For I = DB.TableDefs.Count - 1 To 0 Step -1

         Set tdobj = DB.TableDefs(I)