Unlinking Multiple Tables

[dianawild]

I am working on conversion of data from an existing database to a new database. I have a core database I call the "ConversionDb", where I create all my queries, then I have the old database linked in and the new database linked in.

I get periodic updates of the old database every week. I made the mistake of duplicating all the table links to the old database and I don't know how to unlink the extra ones (each has a 1 appended to the name). When this has happened before, I have gone to each of the 70 tables and deleted the link for each one individually. This is very time consuming.

Is there a way to unlink multiple tables in a single step? I don't mind selecting them all individually, but Access won't let me select multiple tables.

Help would be greatly appreciated.

Thanks,

Diana

 

[boblarson]

Hi there.

You can put this in a standard module:

Function UnlinkAllTablesWithNum()
    Dim z As Integer
    Dim db As DAO.Database
    
    Set db = CurrentDb

    For z = db.TableDefs.Count - 1 To 0 Step -1
        If Right(db.TableDefs(z).Name, 1) = "1" Then
            DoCmd.DeleteObject acTable, db.TableDefs(z).Name
        End If
    Next z
    MsgBox "Complete", vbInformation, "Complete"
End Function

And then call it from the Immediate Window like:


UnlinkAllTablesWithNum


And hit enter.