I use this to refresh the links to all linked tables. As you see it is taking the connection string from a field named ConnectionString in a local table named SettingsTable, but you can modify it to hard code it as a constant in the function itself (I used a public function so it could be called from the AutoExec macro).
Code:
Public Function vcLinkTableDefs()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strNewConnectionString As String
On Error Resume Next
Set dbs = CurrentDb()
' Loop through TableDefs collection, only processing
' the table if it already has a Connection property.
' (all other tables are local ... not linked)
For Each tdf In dbs.TableDefs
If tdf.Connect <> "" Then
strNewConnectionString = DLookup("[ConnectionString]", "[SettingsTable]")
If tdf.Connect <> strNewConnectionString Then
tdf.Connect = strNewConnectionString
tdf.RefreshLink
End If
End If
Next
End Function
If your front-end is linked to multiple back-ends then you need to create a table (tblLinkedTables) with SourceTableName,DestinationTableName,ConnectionStr ing as fields. Once you have that you simply loop though it and refresh the links.
Cheers,
Vlad