Trouble with syntax in VBA for SQL query

Question

I've got the below VBA that I am trying to use to run an SQL query (that works fine in SSMS).

I am not very good at using VBA and am having trouble getting the correct syntax for the SQL query. I'm currently stuck on incorrect syntax near '+', but once this is works, I no doubt will get the same for others!

Can anybody help with this where I am going wrong, or can anyone point me to where I can learn about the correct syntax's required in VBA for SQL?

Thanks in advance!

    Set Conn = CreateObject("ADODB.Connection")
    Set recset = CreateObject("ADODB.Recordset")
        
'set parameters
    ServerSource = Sheets("Sheet1").Range("A1").Value            'choose the server the database is located
    
'insert server name and database name
    sConnect = "Provider=SQLOLEDB.1;" & _
               "Password=ExcelRep0rt;" & _
               "User ID=ExcelReport;" & _
               "Data Source=" & ServerSource & ";" & _
               "Use Encryption for Data=False"
    
    Conn.Open sConnect

'SQL query
    SQLQry = " DECLARE @Sql NVARCHAR(MAX);" & _
                " SET @Sql =" & _
                " STUFF(" & _
                " (SELECT" & _
                " NCHAR (10) + N 'UNION ALL' + NCHAR(10) +" & _
                " N 'SELECT" & _
                " + QUOTENAME(d.name, '''') + N' AS dbName," & _
                " c_broker COLLATE Latin1_General_CI_AS," & _
                " det_costheader COLLATE Latin1_General_CI_AS," & _
                " cuname COLLATE Latin1_General_CI_AS," & _
                " ch_name COLLATE Latin1_General_CI_AS," & _
                " CONVERT(VARCHAR(11), ch_date_req, 103) AS [Flight_Date]," & _
                " IIF(OUT.outstanding > 0, ''Yes'', ''No'') AS [Anything_Outstanding?]," & _
                " sum(c_grossmargin) / 2 AS [Gross_Margin]" & _
                " FROM ' + QUOTENAME(d.name) + N'.dbo.AT_ACS_COMMISSIONS AS COM" & _
                " Left Join ' + QUOTENAME(d.name) + N'.dbo.AT_ACS_PROJECTOUTSTANDING AS OUT" & _
                " ON COM.det_costheader = OUT.project" & _
                " WHERE c_broker = ''HKGCGOJT''" & _
                " GROUP BY det_costheader, c_broker, cuname, ch_name, ch_date_req, outstanding'" & _
                " FROM sys.databases d" & _
                " where name like '%AccountsLive'" & _
                " FOR XML PATH(''), TYPE)" & _
                " .value('text()[1]','nvarchar(max)'), 1, 11, '');" & _
                " exec( @Sql );"
    
'import table - choose range of where to put the table
    Set recset = New ADODB.Recordset
        recset.Open SQLQry, Conn
        Range("B10").CopyFromRecordset recset
        recset.Close

'Import Headers
    'For i = 0 To recset.Fields.Count - 1
    '    Sheets("Sheet1").Range("B9").Offset(0, i) = recset.Fields(i).Name
    'Next i

    Conn.Close
    Set recset = Nothing

Have a look at this stackoverflow.com/questions/20424438/… — Alexey
– Alexey, Commented Mar 15, 2021 at 6:47
Is incorrect syntax near '+' raised by VBA (i.e. when you compile this code) or is it raised when you try and run the SQL? For any dynamically generated SQL, the first step is to print out the result and test it. When you say it "works fine in SSMS", is that you literally printing the value of SQLQry and running it in SSMS? — Nick.Mc
– Nick.Mc, Commented Mar 15, 2021 at 9:00
First thing I see when I search on + in your query is things like this: NCHAR (10) + N 'UNION ALL' It's hard to tell but I really don't think there should be a space between N and a string literal. Anyway.... print out SQLQry and run it in SSMS and I bet you get the same error — Nick.Mc
– Nick.Mc, Commented Mar 15, 2021 at 9:05
The other thingis that you are creating dynamic SQL in a VBA variable called SQLQry which in turn generates dynamics SQL into a SQL variable called @SQL then runs it. You should just generate the dynamic SQL directly in VBA. i.e. generate what goes into @SQL directly in VBA and not go through the middle man.... Or create a stored procedure to do it. — Nick.Mc
– Nick.Mc, Commented Mar 15, 2021 at 9:07
OK. You need to print the value of SQLQry, paste it into SSMS and troubleshoot from there. Use this method. wallstreetmojo.com/vba-debug-print — Nick.Mc
– Nick.Mc, Commented Mar 15, 2021 at 9:24

PeterK · Accepted Answer · 2021-03-16 02:09:23Z

With great direction from @Nick.McDermaid, I followed the below steps from https://www.wallstreetmojo.com/vba-debug-print/:

In VBA: Ctrl+G - bring up the Immediate Window Add Debug.Print SQLQry Run the macro and copy & paste the SQL in the Immediate Window into SSMS (or whatever you're using)

Then can see where you are going wrong in your query.

Just for reference, below is the correct SQL for my particular issue.

SQLQry = " DECLARE @Sql NVARCHAR(MAX);" & _ " SET @Sql =" & _ " STUFF(" & _ " (SELECT" & _ " NCHAR (10) + N'UNION ALL' + NCHAR(10) +" & _ " N'SELECT" & _ " '+ QUOTENAME(d.name, '''') + N' AS dbName," & _ " c_broker COLLATE Latin1_General_CI_AS," & _ " det_costheader COLLATE Latin1_General_CI_AS," & _ " cuname COLLATE Latin1_General_CI_AS," & _ " ch_name COLLATE Latin1_General_CI_AS," & _ " CONVERT(VARCHAR(11), ch_date_req, 103) AS [Flight_Date]," & _ " IIF(OUT.outstanding > 0, ''Yes'', ''No'') AS [Anything_Outstanding?]," & _ " sum(c_grossmargin) / 2 AS [Gross_Margin]" & _ " FROM ' + QUOTENAME(d.name) + N'.dbo.AT_ACS_COMMISSIONS AS COM" & _ " Left Join ' + QUOTENAME(d.name) + N'.dbo.AT_ACS_PROJECTOUTSTANDING AS OUT" & _ " ON COM.det_costheader = OUT.project" & _ " WHERE c_broker = ''HKGCGOJT''" & _ " GROUP BY det_costheader, c_broker, cuname, ch_name, ch_date_req, outstanding'" & _ " FROM sys.databases d" & _ " where name like '%AccountsLive'" & _ " FOR XML PATH(''), TYPE)" & _ " .value('text()[1]','nvarchar(max)'), 1, 11, '');" & _ " exec( @Sql );"

Collectives™ on Stack Overflow

Trouble with syntax in VBA for SQL query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related