ODBC Refresh Connection String Error in VBA / Run Time Error 1004

[RobertoLopez]

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

[Jan Karel Pieterse]

Does it work if you create that same connection manually?

[RobertoLopez]

Yep! This is how the connection string looks like on the Connection Properties-> Connection String:
DSN=AMTECHUSA;Description=AMTECHUSA;UID=lopezro2;Trusted_Connection=Yes;APP =Microsoft Office 2016;WSID=US-CCR1-L08;DATABASE=SKNA_NG

In this case I am changing the DSN, Description and Database in order to be able to retrieve info from different servers and different databases.

Any thoughts?

[Jan Karel Pieterse]

The macro is using range names, the error message you state happens if there is an issue with any of those range names.

[arnelgp]

just maybe:

Sub updateCnnStr()'Variables for workbook and connection
Dim wb As Workbook
'Dim odbc As WorkbookConnection
Dim odbc As ODBCConnection
'Set workbook variable as ThisWorkbook
Set wb = ThisWorkbook
'For each connection string, change DSN, Description, and Database
For Each odbc In wb.Connections
odbc.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")


Next
End Sub