ADO to delete linked sql tables requires multiple passes

[glgcag1991]

I am writing code to delete links to my SQL Server tables and when I run the function, it doesn't delete all the tables. I have a total of 89 linked SQL tables and I have to run the function 5 times to delete all the tables. Here's the code I'm running:

Function DeleteSQLTableLinks()
    'On Error Resume Next
    Dim cn As New ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim tbl As New ADOX.Table
    Dim lCnt As Long
    'Types of tables: TABLE=Access mdb table; ACCESS TABLE=Access mdb system table
    'VIEW=Access mdb query; PASS-THROUGH=SQL Server linked table
    cn.ConnectionString = CurrentProject.Connection
    cn.Open
    cat.ActiveConnection = cn
    lCnt = 0
    For Each tbl In cat.Tables
        If tbl.Type = "PASS-THROUGH" Then
            lCnt = lCnt + 1
            Debug.Print lCnt & ". " & tbl.Name & " --- " & tbl.Type
            cat.Tables.Delete (tbl.Name)
            cat.Tables.Refresh
        End If
    Next tbl
    cn.Close
    Set cn = Nothing
    Set cat = Nothing
End Function

Any help is appreciated!

 

[dhookom]

Your Tables.Refresh causes a reorganizing of the collection. I would count the tables and then step through the tables from the end to the beginning.

Duane
Hook'D on Access
MS Access MVP

 

[glgcag1991]

I removed the tables.refresh and it is still doing the same thing. What do you mean by counting the tables and then stepping through them from end to beginning? I can do it with a recordset but I can't with the cat.tables collection, at least that I know of.

 

[dhookom]

Can you get a cat.Tables.Count value? If so, you start with the Count - 1 and use a for next loop from count-1 to 0 stepping -1.

Duane
Hook'D on Access
MS Access MVP