Hi PJ. I have acouple of functions that will do just that for you. To understand what is happening, go to tools, options and on the general tab check the 'Show System Objects' box. Then from the database window select the MSysObjects table. Filter for non nulls in the 'Connect' column. This will show all of your system objects with connection strings. The following subs and function work together to loop through this table parsing the connection strings and replacing the old DSN with the new one. Create a module and paste this code:
Sub subChangeConnection(strFrom As String, strTo As String)
Dim ws As Workspace
Dim db As Database
Dim tbl As TableDef
Dim intI As Integer
Dim strConnect As String
Dim strTest As String
Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(0)
For intI = 0 To db.TableDefs.count - 1
Set tbl = db.TableDefs(intI)
If Left$(tbl.Connect, 4) = "ODBC" Then
strTest = tbl.name
strConnect = fnChangeConnectString(CStr(tbl.Connect), strFrom, strTo)
If tbl.Connect <> strConnect Then
tbl.Connect = strConnect
tbl.RefreshLink
End If
End If
Next intI
End Sub
These functions change the connection string from DSN ABC to DSN XYZ and vice versa. You of course will have to substitute your from and to DSN's
Function fnChangeAbcToXyz()
Call subChangeConnection("ABC", "XYZ"
End Function
Function fnChangeXyzToAbc()
Call subChangeConnection("XYZ", "ABC"
End Function
I routinely use this to test applications by switching between my Oracle production and test databases.
Hope this helps....T
