Changing back end name and relinking in code

[TimJ]

I have left couple of posts today(Refresh Table Links In Code?) but the
responses I've gotten are not exactly what I'm looking for.

I am not trying to connect to multiple back ends, only 1. The problem is
the name or path of the back end will change.

I want to programmtically (knowing the new name of course) reconnect to th
renamed back. I will have a table with all possible back end names in the
front end. I will loop through the list of possible paths and see if they
exist. As soon as one does I want to connect to it and link or refresh the
table links.

What I am basically looking for is a method by which I can connect to or
refresh the back end already knowing it's path.

Is this possible?

TIA,
TimJ

 

[Allen Browne]

Loop through the TableDefs in the current database.

If it is not a system table (dbSystemObject), and not a temp table (name
starting with ~), and the Connect property is not zero-length (local table),
then set the Connect property to the new path, and RefreshLink.

 

[Nikos Yannacopoulos]

Tim,

Here's a simple procedure to change table links:

Sub change_table_links()
Dim tbl As TableDef
Dim db As Database
Set db = CurrentDb
lnk = "\\SERVERNAME\Shared Folder Name\Your Database Name.mdb"
For i = 0 To db.TableDefs.Count - 1
Set tbl = db.TableDefs(i)
If Left(tbl.Connect, 9) = ";DATABASE" Then
tbl.Connect = ""
tbl.Connect = ";DATABASE=" & lnk
tbl.RefreshLink
End If
Next
End Sub

You can easily include a recordset operation to loop read from the
possible BE table and check for existence with a Dir command. If you
need help with this post back.

HTH,
Nikos