Changing ODBC Connection Programatically to Linked Tables

[Ken V.]

I have an Access 97 front end with a SQL database backend with linked ODBC
tables.

I want to be able to change all linked tables to switch between the
production SQL server and the test SQL server.

I've tried this to no avail. The code runs, and the table shows the new
connection as the connect property (via VBA), but the linked tables are not
updated.

Sub ChangeConnection()
Dim TestTable As TableDef
Const OldConnection = "Valid Connection String"
Const NewConnection = "Valid Connection String"
For Each TestTable In CurrentDb.TableDefs
If TestTable.Connect = OldConnection Then 'Only re-connect select tables
TestTable.Connect = NewConnection
End If
Next
End Sub

 

[Van T. Dinh]

If I remember correctly, you need to refresh the TableDefs Collection of the
CurrentDb. Try adding the statement

CurrentDb.TableDefs.Refresh

after your Next statement.

Make sure that the new Connection String is valid since the code will fail
silently if the new Connection String in invalid. If you not sure try
manually relink one Table and then check the Connection String of the
newly-linked Table.