Unanswered: Replacing connection string info (only the server name) (ODBC) through VBA
I have just converted a large and complex ADP file to compatible 2013 accdb format.
All of the testing was done on test database of identical table names, identical DB name but a different server - so in essence a different DSN
I wrote the code that now modifies the connection string for the Passthrough queries using the QueryDef and a replace function to replace the sever name with the new server name
But how do I write the code to change the connection strings of the tables if they are already using the older dsn can I just point to a new dsn of the same name (the objects within the database are identical) that uses a different server name using the link table manager and all the connection strings will be modified without me having to go through that process of clicking save password and reselecting the primary key like I had to the first time I did this on the test DB. Over 500 tables - do not want to do this again
Sub RelinkTables(ByVal NewDSN As String)
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb ' Can be: Set dbs = "<Path>\<Database>" if the tables are in another Access database (backend).
For Each tdf In dbs.TableDefs
If Len(.Connect) > 0 Then
.Connect = NewDSN
Set dbs = Nothing