Fast Delete of Linked Tables?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bill Sturdevant - 31 Jan 2005 19:23 GMT

I have a front end that links to a slew of tables in a back end. We use this
setup for many clients, but for contractual reasons, we MUST keep each
client's data separated, so I use a combo box to select a client, then loop
through a table containing the names of the tables I need to link to.

Since I might link to multiple backends during a single session, I must
first delete all the linked tables before linking to the new ones, so I loop
through the same table to first delete each linked table, using this
instruction "DoCmd.DeleteObject TableType, TableName" (TableName is the name
of the table and TableType is acTable)

Is there any way to say "Delete all LINKed tables only" in a single
statement? Such a thing would easily cut my delink/link routine in half.

Reply to this Message

Marshall Barton - 31 Jan 2005 20:54 GMT

>I have a front end that links to a slew of tables in a back end. We use this
>setup for many clients, but for contractual reasons, we MUST keep each
[quoted text clipped - 9 lines]
>Is there any way to say "Delete all LINKed tables only" in a single
>statement? Such a thing would easily cut my delink/link routine in half.

No there isn't, but you can shorten your code by using
something like this:

Set db = CurrentDb()
For k = db.TablDefs.Count - 1 To 0 Step -1
    If db.TableDefs(k).Connect <> "" Then
        db.TableDefs.Delete db.TableDefs(k).Name
    End If
Next k

Signature

Marsh
MVP [MS Access]

Reply to this Message