Looping code skipping deleting link tables - ADO 1

[Goondu]

Here's the code.

Public Function DeleteLinkTables()
Dim oCat As New ADOX.Catalog
Dim oTable As New ADOX.Table
Dim sConnString As String
Set oCat = New ADOX.Catalog
oCat.ActiveConnection = CurrentProject.Connection
' Create a new Table object
Set oTable = New ADOX.Table
  For Each oTable In oCat.Tables
    With oTable
      If .Type = "PASS-THROUGH" Then
      oCat.Tables.Delete .Name
      oCat.Tables.Refresh
      End If
    End With
  Next

Set oCat = Nothing
Set oTable = Nothing
End Function

The code works, why is it skipping some of the Link Tables?
Can anybody help?

 

[dhookom]

I expect the oCat tables is requerying so if you delete the 3rd, the 4th now becomes the 3rd and the "Next" is what had been the 5th. This means you ignored the 4th.

Typically you should count the number of tables and start with the last one and move backwards deleting as you go.

Duane
Hook'D on Access
MS Access MVP

 

[Goondu]

Thanks Duane,

How would I go about recoding it? Can you give a sample?

 

[PHV]

What about this ?

Public Function DeleteLinkTables()
Dim oCat As ADOX.Catalog, i As Long
Set oCat = New ADOX.Catalog
oCat.ActiveConnection = CurrentProject.Connection
With oCat.Tables
  For i = .Count - 1 To 0 Step -1
    If .Item(i).Type = "PASS-THROUGH" Then
      .Delete .Item(i).Name
      .Refresh
    End If
  Next
End With
Set oCat = Nothing
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Goondu]

Thanks PHV,

Great stuff, it works. I guess I still need to learn this part ".Item(i).Type" and ".Delete .Item(i).Name".

Doesn't have the VBA intellisense dropdown.