execute a query using DAO

[dcarbone]

I'm trying to execute a query which contains sub queries in VB access.

az is the query name

Set rst = db.Execute(az)

also just copying out the sql code from the query to the vb access code does not work

 

[dcarbone]

also the query requires a parameter to be passed to it

 

[Golom]

Because it's already in Access, you need to run the query there, not in your code ... something like this

Dim Qry As QueryDef
Set Qry = db.QueryDefs(az)
Qry("ParameterName&quot = "123"
Set rs = qdf.OpenRecordset(dbOpenDynaset)

The Execute statement doesn't return recordsets. It is intended for running SQL that doesn't return anything like "Delete", "Update", etc.

 

[GoDawgs]

You need to use a querydef to do this...here's an example from one of my dbs:

Dim RST As Recordset, QDF As QueryDef, SSQL As String

SSQL = "SELECT DISTINCTROW ProcessGroup.Employee_ID FROM..."

Set QDF = CurrentDb.CreateQueryDef("", SSQL)
QDF.Parameters("EnterDepartment&quot = DepartmentCriteria
QDF.Parameters("EnterDate&quot = DateCriteria
Set RST = QDF.OpenRecordset(dbOpenDynaset)

Hope that helps.

Kevin