Code to Detach Tables

SerranoG · February 9th, 2006, 03:38 PM

I've not tried to manipulate tables using Access VBA in this manner, and so I'm not sure how to approach it. Here's the pseudo code of what I want.

Code:

For I = 1 to No. of Tables
    If Table is Linked from another dB Then
        Detach Table
    End If
Next I

I'm using Access XP (2002). Thanks in advance.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

SerranoG · February 9th, 2006, 06:15 PM

Here's what works. The tricky part was that when you actullay dropped a table, the TableDefs.Count changed so you had to adjust the counter for the loop. Also, if the last table was linked, your index ended up past the count because the count dropped by one, so the Exit For was necessary.

Code:

Private Sub UnLinkTables()

    Dim db As Database, tbf As TableDefs
    Dim bytCount As Byte, bytTotal As Byte
    Dim strName As String, fLinked As Boolean, fMsys As Boolean

    Set db = Application.CurrentDb
    Set tbf = db.TableDefs
    bytTotal = tbf.Count

    For bytCount = 0 To bytTotal - 1
        strName = tbf(bytCount).Name
        fMsys = (Left(strName, 4) = "MSys")
        fLinked = (Len(Nz(tbf(bytCount).Connect, "")) > 0)

        If Not fMsys And fLinked Then
            'Not a system table and table IS linked.  OK to drop.
            tbf.Delete strName

            bytTotal = tbf.Count
            bytCount = bytCount - 1
            If bytCount = bytTotal - 1 Then
                'Last table has been dropped.  Index past count.
                Exit For
            End If
        End If
    Next bytCount

    db.Close

    Set tbf = Nothing
    Set db = Nothing

End Sub

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division