Solved

MS Query. Need to load .commandtext with variable but fails.

Posted on 2015-02-24
3
131 Views
Last Modified: 2016-02-11
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.
Sub GetMyInfo()
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DSN=MyDBName;UID=username;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=VirtualXP-PC" _
, Destination:=ActiveCell).QueryTable
'.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"
End With
End Sub
0
Comment
Question by:briandobo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 13

Expert Comment

by:Jeff Darling
ID: 40628519
You can build the array yourself like this:

Dim myExecSQL
Dim myODBCCommand
myExecSQL = "exec MyStoredProc"
myODBCCommand = Array(myExecSQL)

Open in new window

0
 
LVL 34

Accepted Solution

by:
Norie earned 500 total points
ID: 40628565
What happens if you just use this?
ODBCCommand = "exec MyStoredProc"

Open in new window

0
 

Author Comment

by:briandobo
ID: 40628622
WOW, sure thought I tried the:
ODBCCommand = "exec MyStoredProc"

But it worked for me.
Thanks
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question