CTE Passthrough query giving error(3129)

[Kingz]

Hi guys,
I've got a complicated CTE query (strQuery) which works as a passthrough query, so I needed to dynamically set some dates in it, and execute it via VBA. I get an error 3129, meaning that it expects, instead of "with", delete, insert, select, or such likes..
Here is the relevant code:
Dim dbs as database
Dim rs as dao.recordset


Set rs = currentdb.openrecordset (
StrQuery, dbopendynaset, dbSQLPassThrough)


Thanks

 

[Minty]

You need to open it using the correct parameters, so you need to set the query def to return records.
Something like (untested)

strQuery = "; With myCTE as ( blah blah blah)"
Set qDef = db.QueryDefs("qPT_Generic")
qDef.SQL = strQuery
qDef.ReturnsRecords = True
qDef.Close

set rs = Currentdb.OpenRecordset("qpt_Generic", dbOpenSnapshot)

This relies in you setting up a PT query called qpt_Generic that you utilise for this purpose.

 

[Kingz]

You need to open it using the correct parameters, so you need to set the query def to return records.
Something like (untested)

strQuery = "; With myCTE as ( blah blah blah)"
Set qDef = db.QueryDefs("qPT_Generic")
qDef.SQL = strQuery
qDef.ReturnsRecords = True
qDef.Close

set rs = Currentdb.OpenRecordset("qpt_Generic", dbOpenSnapshot)

This relies in you setting up a PT query called qpt_Generic that you utilise for this purpose.

Fantastic!! Thanks for that!