Re: strange behavior by deleting tables

"Cesar Romani" <cesar_romani@xxxxxxxx> wrote in message
news:kHrRh.19564$tx6.4142@xxxxxxxxxxxxxxxxxxxxxxxx
I've written the following code to delete all tables of a ms access db.
It deletes only half the number of tables not all.
----------------------------------------
1. Sub deleteTables()
2. Dim cat As New ADOX.Catalog
3. Dim tbl As ADOX.Table
4.
5. cat.ActiveConnection = CurrentProject.Connection
6.
7. For Each tbl In cat.Tables
8. If tbl.Type = "TABLE" Then
9. cat.Tables.Delete tbl.Name
10. End If
11. Next
12.
13. Set tbl = Nothing: Set cat = Nothing
14. End Sub
------------------------------------------
If I change the line 9. with
DoCmd.DeleteObject acTable, tbl.Name
the modified code deletes all tables. Why is it so?

Many thanks in advance,
Cesar

Any time you delete objects by looping a collection you have to loop backwards
or this happens. When you loop forward and delete object(0) the object that WAS
object(1) becomes the new object(0) and you move onto object(1) skipping an
object. That continues all the way through. If you start at the last object
and go backwards no re-positioning occurs and all objects are deleted.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com



.

Relevant Pages