I have some code for refreshing links that I thought was working. I did make some minor modification to handle the case where the front end and back end names were different. Now for some reason the ODBC Connect strings are not getting refreshed, even on the tables where the names are the same.
The code, see below, renames the old table, creates a new table link, then deletes the original link. The code works, in that the tables are correctly linked. But when I use the Connect string later in other code it is the old string!
When I trace through the code I see where it changes the .Connect property, and newConnectionString has the correct value. But even after the db.TableDefs.Refresh line the table still has the old connection string!
What am I missing?
The code, see below, renames the old table, creates a new table link, then deletes the original link. The code works, in that the tables are correctly linked. But when I use the Connect string later in other code it is the old string!
When I trace through the code I see where it changes the .Connect property, and newConnectionString has the correct value. But even after the db.TableDefs.Refresh line the table still has the old connection string!
What am I missing?
Code:
Public Sub RefreshODBCLinks(strTableName As String, newConnectionString As String, strSourceName As String, lngAttributes As Long)
Dim db As DAO.Database
Dim tb As DAO.TableDef
Dim originalname As String
Dim tempname As String
Dim sourcename As String
Dim cleanName As String
Dim i As Integer
On Error GoTo Proc_Err
Set db = CurrentDb
' Create new tables using the given DSN after moving the old ones '
originalname = strTableName
tempname = "~" & originalname & "~"
' Create the replacement table '
db.TableDefs(originalname).Name = tempname
cleanName = Replace(originalname, "~", vbNullString)
Set tb = db.CreateTableDef(cleanName)
If lngAttributes <> 0 Then
tb.Attributes = lngAttributes
End If
tb.SourceTableName = strSourceName
tb.Connect = newConnectionString
db.TableDefs.Append tb
Err = 0
On Error Resume Next
tb.RefreshLink ' Relink the table.
If Err <> 0 Then
MsgBox "Unable to Relink table " & tb.Name & vbCrLf & vbCrLf & _
"Some tables may have been relinked. Database link condition is unknown."
mbolRelink = False
GoTo Proc_Exit
End If
' delete the old table '
DoCmd.DeleteObject acTable, tempname
db.TableDefs.Refresh
'Debug.Print "Refreshed ODBC table " & originalname
Proc_Exit:
On Error Resume Next
Set db = Nothing
Exit Sub
Proc_Err:
mbolRelink = False
MsgBox "Error frmRelink function 'Relink' " & vbCrLf & vbCrLf & Error$
Resume Proc_Exit
End Sub
