[Last Call] Learn how to a build a cloud-first strategyRegister Now


sql query gives error via vba but exact query runs fine outside of vba

Posted on 2014-08-19
Medium Priority
Last Modified: 2014-08-25
I use an ADO connection to a DB2 for i server to pull data into Excel.  I do this often so in general I'm pretty good at it ;-)

Recently I've encountered an error on a new query, or should I say query with multiple statements, that is erroring out.

As a matter of habit I always insert the 'string' of my sql that vba is executing to a cell in the workbook.  This way, If needed, I can verify all my variables (cell references) were passed as expected and I can troubleshoot from this.

In my current 'problem', I am getting the attached error even though I can copy/paste the exact code from the spreadsheet into my query tool and run without issue.

Below this you will find the code I use to build my string that returns the error.  Note that this includes a semicolon as required between execution 'pieces' as required by SQL.
exact VBA
SQL = " CREATE ALIAS temp.kd2 FOR cust.job(SAV0" & Range("B3").Value & ") ; "
SQL = SQL + " select * from temp.kd2  ;"
SQL = SQL + " DROP ALIAS temp.kd2 ;   "

Exactly what I get from the cell where I have the 'sql' string put into.
 CREATE ALIAS temp.kd2 FOR cust.job(SAV0554158) ;  select * from temp.kd2  ;   DROP ALIAS temp.kd2 ;  

As I mentioned, running that exact query in my query tool works perfectly.  Thus, I know the query itself is fine.  There must be some issue with using ADO connections and multiple code execution or something else that I am missing.

I cross posted this because while I am using DB2, I do not believe this has anything to do with the DB since the query itself is valid.  I expect this has something to do with VBA and the ; symbol.

Question by:Ray

Expert Comment

ID: 40270993
Try changing the double quotes to single quotes.
LVL 11

Author Comment

ID: 40271001
Those double quotes are a requirement of VBA and are used to encapsulate the text string on each line.  Can't remove them.
LVL 37

Accepted Solution

momi_sabag earned 2000 total points
ID: 40271554
you are trying to execute 3 sql statements in one call
the query tool knows automatically to break the input on ; and execute the commands in order
try to execute the 3 commands on after the other and you should be good to go
LVL 35

Expert Comment

ID: 40272307
Couldn't you just use this?
SQL = " SELECT * FROM cust.job(SAV0" & Range("B3").Value & ") ; "

Open in new window

LVL 11

Author Closing Comment

ID: 40283500
Sadly, this is indeed the issue.  When running the query through Excel/VBA, you cannot execute multiple statements via the statement termination character.  

The workaround is to execute each one independently and in order.  I was afraid of this by the time I posted the question, but based on further research and the responses here... a Workaround is the only way to do it.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

829 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