Code Not Working For Deleting All Linked Tables

[Gary S]

When I run the following code, about half of the
linked tables are deleted. If I run it several times
it eventually gets them all. Why is that??????

Dim db2 As Database
Dim tdf2 As DAO.TableDef
Dim TableType As String
Dim TableName As String

Set db2 = CurrentDb

MsgBox "Total Tables in Current Database: " & db2.TableDefs.Count

For Each tdf2 In db2.TableDefs

TableName = tdf2.Name

TableType = "Regular"

If Left(TableName, 4) = "Msys" Then
TableType = "System"
End If

If tdf2.RecordCount = -1 Then
TableType = "Linked"
End If

If TableType = "Linked" Then
db2.TableDefs.Delete TableName
End If

Next

Set db2 = Nothing

 

[Douglas J. Steele]

You need to loop backwards through the list of tables. A better way to
determine whether the table is linked is to check its Connect property. If
it's not a zero-length string, it's a linked table. (System tables will
always have a zero-length string for the Connect property)

Dim db2 As Database
Dim tdf2 As DAO.TableDef
Dim lngLoop As Long
Dim TableType As String
Dim TableName As String

Set db2 = CurrentDb

MsgBox "Total Tables in Current Database: " & db2.TableDefs.Count

For lngLoop = (db2.TableDefs.Count - 1) To 0 Step -1
Set tdf2 = db2.TableDefs(lngLoop)
TableName = tdf2.Name
If Len(tdf2.Connect) > 0 Then
db2.TableDefs.Delete TableName
End If
Next lngLoop

Set db2 = Nothing

 

[Gary S]

Doug,
Thanks for your response.

I am curious, why is it necessary to
loop backwards through the recordset?
--
Thanks for your help!
Gary S

You need to loop backwards through the list of tables. A better way to
determine whether the table is linked is to check its Connect property. If
it's not a zero-length string, it's a linked table. (System tables will
always have a zero-length string for the Connect property)

Dim db2 As Database
Dim tdf2 As DAO.TableDef
Dim lngLoop As Long
Dim TableType As String
Dim TableName As String

Set db2 = CurrentDb

MsgBox "Total Tables in Current Database: " & db2.TableDefs.Count

For lngLoop = (db2.TableDefs.Count - 1) To 0 Step -1
Set tdf2 = db2.TableDefs(lngLoop)
TableName = tdf2.Name
If Len(tdf2.Connect) > 0 Then
db2.TableDefs.Delete TableName
End If
Next lngLoop

Set db2 = Nothing

 

[pietlinden]

Doug,
Thanks for your response.

I am curious, why is it necessary to
loop backwards through the recordset?

because if you try to delete from 0 to 9, once you have deleted
item(0), item(9) becomes item(8) and so on.