MS Query. Need to load .commandtext with variable but fails.
Posted on 2015-02-24
I have a macro where I run a MS Query several times but I want to run a different SQL Stored Procedure each time.
I am trying to load the .commandtext with data from a variable. This way I just need to change the variable and execute the same query instead of running 5 different queries.
I load the variable with the correct stored procedure to run in the .commandtext, but the query fails with syntax error.
ODBCCommand = "Array(" & Chr(34) & "exec MyStoredProc" & Chr(34) & ")"
This is the subroutine to run the MS Query.
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DSN=MyDBName;UID=username;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=VirtualXP-PC" _
'.CommandText = Array("exec MyStoredProc") <This will work but I have it commented out to use variable instead.
.CommandText = ODBCCommand <Even though variable is correct, query fails with "SQL Syntax error"
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False <<This is where it fails with "SQL Syntax error"