Re: strange behavior by deleting tables

Rick Brandt wrote:
"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.

But that doesn't happen with the modified code. If I change the line 9. with
DoCmd.DeleteObject acTable, tbl.Name
the subroutine deletes all the tables so your explaining doesn't apply in
that case.
I think that's a problem with cat.Tables.Delete not with the loop.

Thanks,

Cesar


.

Relevant Pages