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:
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?
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:
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
2) The MSysObjects table does not get a new value in the Connect column
Suggestions please?