Iteration through field collection not identifiying all table fields

[bdbBear]

I have the following code wherein I am removing all (but one) fields from a table. I am identifying the field names by iterating through the field collection of that table.

Dim dbs As DAO.Database
Set dbs = CurrentDb

Dim t As DAO.TableDef
Set t = dbs.TableDefs("tblCorrelations")

Dim fld As Field

For Each fld In t.Fields
Debug.Print fld.Name
If fld.Name <> "Company Name" Then
t.Fields.Delete fld.Name
End If
Next fld

Set fld = Nothing


For some reason this doesn't identify all of the fields (they are all number/double).

After I run the code and open up the table it shows that some of the fields were deleted but some are still there.

If I run the code again, then some more of the fields are removed and some still remain.

It seems as if the iteration through the field collection is random.

Anyone have any ideas on how to solve this problem?

Thanks!

- Bruce

 

[PHV]

Dim i As Integer
For i = t.Fields.Count-1 To 0 Step -1
Set fld = t.Fields(i)
Debug.Print fld.Name
...
Next i

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bdbBear]

Thanks PH!