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.
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.