VBA Exec SQL Statement

Question

I've been looking at this statement for ages and I'm sure its something minor. However I cannot get it to pull the data through via VBA. Strange thing is it works in SQL as a statement but as soon as I run it through VBA I get a runtime error. I could really do with a second pair of eyes.

stSQL = "EXEC('SELECT T1.Name FROM [dbo].[Tbl1] T1 " & _
            "INNER JOIN [dbo].[Tbl2] T2 ON T1.ID = T2.ID " & _
            "WHERE T2.NAME = '" & sBizUnit & "' AND Desc = '''' ORDER BY Name')"

sBizUnit is a value from a combobox:

sBizUnit = CB_BizU.Value

Thanks in advance

talegna · Accepted Answer · 2014-01-10 10:48:37Z

2

Does the account you are running your application under have permission to run the EXEC command?

Another point I've noticed is that you haven't escaped the first set of quotation marks, maybe try:

stSQL = "EXEC('SELECT T1.Name FROM [dbo].[Tbl1] T1 " & _
        "INNER JOIN [dbo].[Tbl2] T2 ON T1.ID = T2.ID " & _
        "WHERE T2.NAME = ''" & sBizUnit & "'' AND Desc = '''' ORDER BY Name')"

answered Jan 10, 2014 at 10:48

talegna

2,3992 gold badges21 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Exec SQL Statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related