ODBC Linked Table Refresh or Change VBA (1 Viewer)

[Tango]

I have been putting together a way to re-link ODBC linked tables through VBA in Access. At times our server name changes so this code will prompt the user to provide a new server name without requiring the user to get involved with the linked table manager or using a DSN file. Just tie this code to whatever trigger you need, I use a command button on a form for mine.

Dim dbs As DAO.Database
  Dim tdf As DAO.TableDef
  Dim Newserver As String
 
Newserver = InputBox("Enter the network name of the server you wish to use:" & vbCrLf & vbCrLf & "Example: SSC-CS-SQL02", _
                           "Bind To New Server")
 
  'If the user clicks cancel or gives a null length responce the server change will abort
 
If Newserver <> "" Then
MsgBox "Server Selected: " & Newserver
  Set dbs = CurrentDb()
  ' Loop through TableDefs collection, only processing
  ' the table if it already has a Connection property.
  ' (all other tables are local ... not linked.) 
  ' In other words this will only change linked tables.
  For Each tdf In dbs.TableDefs
    If tdf.Connect <> "" Then
      tdf.Connect = "ODBC;DRIVER={SQL Server};SERVER=" & Newserver & ";DATABASE=QP3;Trusted_Connection=Yes"
      tdf.RefreshLink
    End If
  Next
Else
MsgBox "Server change request canceled or invalid responce given."
End If