UPDATE: I tried a different approach and it seemed to work; the
procedure looped through all the linked tables listed in the control
table (which I modified to have only a list of linked table names) and
successfully relinked them all.
Unfortunately, it did not preserve the indexes that were set when the
tables were originally linked. I need to be able to preserve the
index. Even better would be a way to do this same thing without having
to employ a control table listing all the linked objects. That is, a
way to loop through all linked objects of a certain type and perform
the same replace operation. I actually tried to do that on my 2nd
attempt by adding ".Properties.Refresh" and ".Indexes.Refresh" to the
code, but that didn't work (as documented below).
Here's an example of the code I used (adapted from a microsoft support
article): (stuff surrounded by brackets are generic things I'm
substituting for the "real" server names, by the way)
'*******************************************************
' code adapted from microsoft by jwp
'*******************************************************
Function ChangeLink()
'adapted from
http://support.microsoft.com/default...b;en-us;172347
'need table containing dsn names to loop through
'remmed out lines not needed
'Function ChangeLink(strLinkName As String, strDSNName As String, _
' Optional IsFileDSN As Boolean)
Dim db As Database, tdf As TableDef
Dim rs As Recordset
' changed from original microsoft code
' Dim strConn As String
Dim strConn As Variant
' originally added these variables -- but they are not needed
' Dim strLinkName As Variant
' Dim strDSNName As Variant
' Dim strNewDSNName As Variant
On Error GoTo Errorhandler
Set db = CurrentDb
' open table containing dsn names and linked table names
Set rs = db.OpenRecordset("tblReconnect", dbOpenSnapshot)
' now start looping through records in tblreconnect
With rs
.MoveFirst
Do While Not .EOF
strLinkName = rs!tablename
' originally included these variables -- no longer needed
' strDSNName = rs!OldConnectString
' strNewDSNName = rs!NewConnectString
strConn = db.TableDefs(strLinkName).Connect
' now do the search/replace -- only DSN part and SRVR part
' of connect string needs to change -- the TABLE part remains the same
' STEP ONE
' Find all occurrences of [1st old server name] in DSN part of connect
string ...
If InStr(strConn, "ODBC;DSN=[old name]") > 0 Then
' and replace them with [1st new server name]
newstrConn = Replace(strConn, "ODBC;DSN=[old name]", "ODBC;DSN=[new
name]")
End If
' STEP TWO
' Find all occurrences of [2nd old server name] in DSN part of connect
string ...
If InStr(strConn, "ODBC;DSN=[old name2]") > 0 Then
' and replace them with [2nd new server name]
newstrConn = Replace(strConn, "ODBC;DSN=[old name2]", "ODBC;DSN=[new
name2]")
End If
' STEP THREE
' Find all occurrences of [1st old server name] in SRVR part of connect
string ...
If InStr(strConn, "SRVR=[old name]") > 0 Then
' and replace them with [1st new server name]
newstrConn = Replace(strConn, "SRVR=[old name]", "SRVR=[new name]")
End If
' STEP FOUR
' Find all occurrences of [2nd old server name] in SRVR part of connect
string ...
If InStr(strConn, "SRVR=[old name2]") > 0 Then
' and replace them with [2nd new server name]
newstrConn = Replace(strConn, "SRVR=[old name2]", "SRVR=[new name2]")
End If
' Link with the new connect string.
With db.TableDefs(strLinkName)
.Connect = newstrConn
.RefreshLink
' added next 2 lines in an attempt to refresh table indexes
' and description -- but they did not work
.Properties.Refresh
.Indexes.Refresh
End With
' now loop to next record in control table
.MoveNext
Loop
End With
Exit Function
Errorhandler:
MsgBox Err & " " & Err.Description
Exit Function
'********************************************
That's it. Whatever advice or direction would be greatly appreciated.
Similar Threads
