RobertoLopez
08-03-2021, 12:15 PM
I am having troubles with a VBA macro that used to run great before but now it suddenly stop working for one of my files. The macro objective is to update the ODBC Query connection string based on different parameters inputted on several cells.
I used the debug function to find the problem. It looks like the problem originates at the "With" statement. The error windows shows: "Application-defined or object-defined error"
Attached you will find the code.
Sub updateCnnStr()
'Variables for workbook and connection
Dim wb As Workbook
Dim odbc As WorkbookConnection
'Set workbook variable as ThisWorkbook
Set wb = ThisWorkbook
'For each connection string, change DSN, Description, and Database
For Each odbc In wb.Connections
With odbc.ODBCConnection
.Connection = _ "ODBC;DSN=" & wb.Worksheets(1).Range("DSN") & ";Description=" & wb.Worksheets(1).Range("Description") & ";UID=lopezro2;APP=Microsoft Office 2016;WSID=US-CCR1-L08;DATABASE=" & wb.Worksheets(1).Range("Database")
End With
Next
End Sub
I used the debug function to find the problem. It looks like the problem originates at the "With" statement. The error windows shows: "Application-defined or object-defined error"
Attached you will find the code.
Sub updateCnnStr()
'Variables for workbook and connection
Dim wb As Workbook
Dim odbc As WorkbookConnection
'Set workbook variable as ThisWorkbook
Set wb = ThisWorkbook
'For each connection string, change DSN, Description, and Database
For Each odbc In wb.Connections
With odbc.ODBCConnection
.Connection = _ "ODBC;DSN=" & wb.Worksheets(1).Range("DSN") & ";Description=" & wb.Worksheets(1).Range("Description") & ";UID=lopezro2;APP=Microsoft Office 2016;WSID=US-CCR1-L08;DATABASE=" & wb.Worksheets(1).Range("Database")
End With
Next
End Sub