CTE Passthrough query giving error(3129) (1 Viewer)

Kingz

Member
Joined
Mar 19, 2024
Messages
49
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
 
You need to open it using the correct parameters, so you need to set the query def to return records.
Something like (untested)

SQL:
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.
 
You need to open it using the correct parameters, so you need to set the query def to return records.
Something like (untested)

SQL:
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!
 

Users who are viewing this thread

Top Bottom