Where can I change the connection to a SQL db in Access 2013?

Question

I can not seem to find the place that allows me to change the connection to linked tables in Access 2013. Mine is currently pointing to a test database and I want to change that to the production DB.

All the tables in the linked table manager show "DSN = MyDsn;Database=MyDataTest" and I want to change it to "DSN=MyDSN;Database=MyData". And I would like to do this without removing and relinking each table. If I have to relink each table then I have to go back and rename each link because it always prefaces the table name with the schema name.

Answer 1

You can try the following VBA code, which I haven't fully tested.

Public Sub RefreshLinks()
Dim tdf As DAO.TableDef
Dim db As Database
Set db = CurrentDb()
For Each tdf In db.TableDefs
    If VBA.Left$(tdf.Name, 4) <> "MSys" 
        If (tdf.Attributes And dbAttachedTable) = dbAttachedTable Then
            tdf.Connect = "DSN=MyDSN;Database=MyData"
            tdf.RefreshLink
        End If
    End If
Next tdf
End Sub