Microsoft KB Archive/129928

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

Article ID: 129928

Article Last Modified on 12/9/2003

-

APPLIES TO


 * Microsoft Visual Basic 4.0 Standard Edition
 * Microsoft Visual Basic 4.0 Professional Edition
 * Microsoft Visual Basic 4.0 Professional Edition
 * Microsoft Visual Basic 4.0 16-bit Enterprise Edition
 * Microsoft Visual Basic 4.0 32-Bit Enterprise Edition

-



This article was previously published under Q129928



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.



Steps to Reproduce Behavior
 Start a new project in Visual Basic. Form1 is created by default.  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   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  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.  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) </li></ol>

Additional query words: 4.00 vb4win vb4all

Keywords: kbprb KB129928

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.