Setting an ODBC connection string in VBA

Question

I have created a macro that sends a new CommandText to an ODBC Connection in my Excel spreadsheet and then refreshes the table of results associated with the query. This has been working fine, but I've noticed that each time I run the macro it overwrites the connection string with some default values that work on my machine but will not work on other users' machines because they do not have the saved connection file that I have. The more specific connection string that specifies a server address works when entered manually, but will get overwritten anytime the macro is run.

I figured I would just have the macro write the connection string at the same time it sends the new CommandText, but I'm running into errors.

My code is as follows:

Sub NewData()

Dim lStr As String
lStr = ""
lStr = lStr & " USE myDBname; "
lStr = lStr & " WITH X AS ("
lStr = lStr & " SELECT"
lStr = lStr & " column1, column2, column3, etc"
lStr = lStr & " FROM"
lStr = lStr & " etc. etc. etc."

With ActiveWorkbook.Connections("PayoffQuery").ODBCConnection

.CommandText = lStr
.Connection = "SERVER=myserveraddress;UID=SYSTEM;Trusted_Connection=Yes;APP=2007 Microsoft Office system;WSID=SYSTEM;DATABASE=myDBname;"

End With

End Sub

The .CommandText still updates just fine, but the .Connection throws runtime error 1004: Application-defined or object-defined error.

Any idea what I'm doing wrong here? TIA.

Answer 1

In your VBA code, add ODBC; to the beginning of your new connection string.

.Connection = "ODBC;SERVER=myserveraddress;UID=SYSTEM;Trusted_Connection=Yes;APP=2007 Microsoft Office system;WSID=SYSTEM;DATABASE=myDBname;"