I’m trying to build a SQL statement in VBA that I can run to generate a recordset.
The difficulty is that this is a complex statement that requires me to build a statement in SQL and then run it by executing sp_executesql. But the recordset isn’t even being opened.

I build all of the SQL in VBA and store it in a string. I have a global variable rst defined as an ADODB.Recordset. but when I execute rst.open SQL, the recordset doesn’t open and doesn’t give an error.

I tried this simple test to try and help identify the problem.
And this works.

SQL = “Select * from Risks”
rst.open SQL

Print rst.fields.count gives me 69
Print rst.fields.eof returns a value of False
So my connectionstring and my recordset are working.

Next, I created a very simple SQL statement that used sp_executesql.

sql2 = “declare @sql NVARCHAR(MAX) = ”; set @sql = ‘Select * from Risks;’; execute sp_executesql @sql;”
rst.open SQL2

Then if I print rst.fields.count, I get 0.
If I print rst.eof, I get an error message:
Run-time error ‘3704’:
Operation is not allowed when the object is closed.
So I’m not getting an SQL error about syntax; I don’t know what is happening.

The code runs in SQLQuery.
I’m wondering if I can’t execute sp_executesql code passed to it in a recordset??
And if that’s the case, what’s the workaround?