Problem deleting all linked tables 2

[bbarr]

I created the following code to delete all linked tables in my database (Access 200):

--------------------------------------------------------
Dim tbl As TableDef

Dim dbs As Database
Set dbs = CurrentDb

For Each tbl In dbs.TableDefs
If Len(tbl.Connect) > 0 Then ' Is a linked table
Debug.Print "Deleting table " & tbl.name & " ... "
dbs.TableDefs.Delete tbl.name
End If
Next tbl

---------------------------------------------------------

When I execute the code it goes through the collection of tables and deletes "some" of the linked tables.

If I execute the code again, it again deletes "some" of the linked tables.

Thus, in order to delete all linked tables I have to execute this code several times.

My question is, since I'm using the connection string to determine if the table is a linked table, then why does it return true some times and false on other times for the same table? I'm assuming this is what is happening as it is the only thing that makes sense.

Thanks for your help.

- Bruce

 

[PHV]

Try something like this :
For i = dbs.TableDefs.Count -1 To 0 Step -1
If Len(dbs.TableDefs(i).Connect) > 0 Then ' Is a linked table
Debug.Print "Deleting table " & dbs.TableDefs(i).Name & " ... "
dbs.TableDefs.Delete dbs.TableDefs(i).Name
End If
Next i

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

 

[bbarr]

It worked!

but why?

 

[bbarr]

forgot to say thanks!

 

[PHV]

Because browsing the collection downward will prevent the Loop iteration to be broken by the deletion.
Sorry for my poor english.

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

 

[bbarr]

do you mean that if i delete something in position 5 of a collection then whatever is in position 6 moves to position 5 but the counter moves to position 6 which now has a different value?

 

[PHV]

Yes, there is NEVER a hole in collections.

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

 

[bbarr]

Thanks again!

 

[flyover789]

PHV

very interesting and helpful
I've had a similar issue with dynamically creating datasheet subforms ... and even the sample-code in the MS Help doesn't consider this ... !

merci beaucoup.

fly

Martin Serra Jr.

http://www.mserrasystems.com