delete all links in front-end

[Christopher Glaeser]

Is there any easy way to select and delete all links in a front-end at once?
Currently, I'm selecting and deleting each link one at a time, answering
"yes" each time the dialog box appears, a rather tedious process when
deleting dozens of links.

Best,
Christopher

 

[Guest]

Hi Christopher,
I think I may have a solution, I did do a unlink function once. Here is the
just if it

First build a table that contains a list of the tables in the database.

Next build a function that opens this table and then loops through the
recordset.

The command you need is DoCmd.DeleteObject acTable, 'table name'

What then happens is you fill a var with the table name in sequence and run
the docmd on each loop, and hey presto you have no links.

You can use another Docmd command to re-link the database if you like also,

DoCmd.TransferDatabase acLink

look it up in the help, as this is the best way to learn.

You could, with these two commands build a simple interface that will link
and un-link a database with the klick of a button.

Have fun,

Mike J. Soames

ps. If you really get stuck just shout.

 

[John Spencer]

It can be done with some vba (see below).

The question is why you would need to do this. Normally the table structure
should be very stable.

You can save the following code in a module a call it from a button or the
immediate window. I would add some code to this routine or the calling
routine to warn the user on what is about to happen. Let them cancel if
they are not sure.

'----------------- Code Begins ---------------------
Public Function deleteTables() As Boolean

Dim db As DAO.Database
Dim tblAny As DAO.TableDef
Dim intLoop As Integer
Dim intTableCount

Set db = CurrentDb()
intTableCount = db.TableDefs.Count - 1

'loop through tables from highest to lowest
'to handle Access renumbering the collection
'as you delete from the collection
For intLoop = intTableCount To 0 Step -1
Set tblAny = db.TableDefs(intLoop)
'test here to make sure table
'is linked table
If Len(tblAny.Connect) <> 0 Then
db.TableDefs.Delete tblAny.Name
End If

Next intLoop

End Function

 

[Christopher Glaeser]

The question is why you would need to do this. Normally the table

structure should be very stable.

I need to delete all links because it's the only way I know how to change
all the links after moving a back-end. After moving the back-end, I delete
all the links in the front-end, and then use the link wizard to reestablish
all the links. Is there a better approach?

Best,
Christopher

 

[John Spencer]

YES there is a better approach.

There is code at
http://www.mvps.org/access/tables/tbl0009.htm

that will do this. You need to also grab the code at
http://www.mvps.org/access/api/api0001.htm

The code looks daunting, but it works and all you really need to do is call
the code once you have copied it into a module.