I created a macro that sends a new CommandText to ODBC Connection in my Excel spreadsheet, and then updates the table of results associated with the query. This works fine, but I noticed that every 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' computers because they do not have a saved connection file what I have. A more specific connection string that defines the server address works when entered manually, but will be overwritten at any time when the macro is executed.
I decided that I just want the macro to record the connection string while sending a new CommandText, but I am encountering 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
.CommandText still only updates perfectly, but .Connection throws a 1004 runtime error: User-defined or object error.
Any idea what I'm doing wrong here? TIA.
source
share