Here is some code I use to change linked tables from one SQL Server to another (from development to production):
Code:
Set dbs = CurrentDb()
' Loop through TableDefs collection, only processing
' the table if it already has a Connection property.
' assumes all other tables are local ... not linked to other back-ends
For Each tdf In dbs.TableDefs
If tdf.Connect <> "" Then
strNewConnectionString = DLookup("[ConnectionString]", "[SettingsTable]")
'strNewConnectionString = '"Enter your new connections string; open the msysObjects table and look at the Connect field for a table you already updated and copy that here"
If tdf.Connect <> strNewConnectionString Then
tdf.Connect = strNewConnectionString
tdf.RefreshLink
End If
End If
Next
As you can see I store the connection string in a field called "ConnectionString" in a table named "SettingsTable", you can edit to match your setup or comment out that line and use the next to hard code it.
Cheers,