Trying to update Linked Table ODBC settings via ADO

[mdlueck]

I am taking small steps towards having the SQL Server connection settings only in one spot for an application I am developing. That spot to be the Windows Registry.

I have build some VBA code to update the ODBC connection settings using ADO objects. The code appears to execute without error. However the ODBC settings are not getting updated.

Here is the code:

'This API updates the ODBC connection settings to the remote SQLServer database
Public Function UpdateConnectionsBasedOnTypePassThrough(ThisConnectString As String) As Integer
On Error GoTo Err_UpdateConnectionsBasedOnTypePassThrough

  Dim adoCat As New ADOX.Catalog
  Dim adoTbl As New ADOX.Table

  'Define attachment to FE database
  Set adoCat = New ADOX.Catalog
  Set adoCat.ActiveConnection = CurrentProject.Connection
  Set adoTbl.ParentCatalog = adoCat

  'Refresh Linked tables
  For Each adoTbl In adoCat.Tables
      If adoTbl.Type = "PASS-THROUGH" Then
          adoTbl.Properties("Jet OLEDB:Link Provider String") = ThisConnectString
      End If
  Next

  'Good return code
  UpdateConnectionsBasedOnTypePassThrough = 0

Exit_UpdateConnectionsBasedOnTypePassThrough:
  'Clean up the connection to the database
  Set adoTbl = Nothing
  Set adoCat = Nothing

  Exit Function

Err_UpdateConnectionsBasedOnTypePassThrough:
  Call errorhandler_MsgBox("Class: clsObjMSysObjectsTbl, Function: UpdateConnectionsBasedOnTypePassThrough()")
  UpdateConnectionsBasedOnTypePassThrough = -1
  Resume Exit_UpdateConnectionsBasedOnTypePassThrough

End Function

1) I am not seeing the settings updated in the Navigation Pane \ mouse hover text update to the new ODBC settings.
2) The MSysObjects table does not get a new value in the Connect column

Suggestions please?

 

[mdlueck]

I post this code, and it begins working flawlessly... hhhhmmmm........

Proceeding forward from this challenge for the moment.