QueryDefs Collection (DAO)

A QueryDefs collection contains all QueryDef objects of a Database object in a Microsoft Access database engine database.

Remarks

To create a new QueryDef object, use the CreateQueryDef method. In a Microsoft Access workspace, if you supply a string for the name argument or if you explicitly set the Name property of the new QueryDef object to a non–zero-length string, you will create a permanent QueryDef that will automatically be appended to the QueryDefs collection and saved to disk. Supplying a zero-length string as the name argument or explicitly setting the Name property to a zero-length string will result in a temporary QueryDef object.

To refer to a QueryDef object in a collection by its ordinal number or by its Name property setting, use any of the following syntax forms:

QueryDefs(0)

QueryDefs("name")

QueryDefs![name]

You can refer to temporary QueryDef objects only by the object variables that you have assigned to them.

Example

This example creates a new QueryDef object and appends it to the QueryDefs collection of the Northwind Database object. It then enumerates the QueryDefs collection and the Properties collection of the new QueryDef.

Sub QueryDefX() 
 
   Dim dbsNorthwind As Database 
   Dim qdfNew As QueryDef 
   Dim qdfLoop As QueryDef 
   Dim prpLoop As Property 
 
   Set dbsNorthwind = OpenDatabase("Northwind.mdb") 
 
   ' Create new QueryDef object. Because it has a  
   ' name, it is automatically appended to the  
   ' QueryDefs collection. 
   Set qdfNew = dbsNorthwind.CreateQueryDef("NewQueryDef", _ 
         "SELECT * FROM Categories") 
 
   With dbsNorthwind 
      Debug.Print .QueryDefs.Count & _ 
         " QueryDefs in " & .Name 
 
      ' Enumerate QueryDefs collection. 
      For Each qdfLoop In .QueryDefs 
         Debug.Print "  " & qdfLoop.Name 
      Next qdfLoop 
 
      With qdfNew 
         Debug.Print "Properties of " & .Name 
 
         ' Enumerate Properties collection of new  
         ' QueryDef object. 
         For Each prpLoop In .Properties 
            On Error Resume Next 
            Debug.Print "  " & prpLoop.Name & " - " & _ 
               IIf(prpLoop = "", "[empty]", prpLoop) 
            On Error Goto 0 
         Next prpLoop 
      End With 
 
      ' Delete new QueryDef because this is a  
      ' demonstration. 
      .QueryDefs.Delete qdfNew.Name 
      .Close 
   End With 
 
End Sub 

This example uses the CreateQueryDef method to create and execute both a temporary and a permanent QueryDef. The GetrstTemp function is required for this procedure to run.

Sub CreateQueryDefX() 
 
   Dim dbsNorthwind As Database 
   Dim qdfTemp As QueryDef 
   Dim qdfNew As QueryDef 
 
   Set dbsNorthwind = OpenDatabase("Northwind.mdb") 
 
   With dbsNorthwind 
      ' Create temporary QueryDef. 
      Set qdfTemp = .CreateQueryDef("", _ 
         "SELECT * FROM Employees") 
      ' Open Recordset and print report. 
      GetrstTemp qdfTemp 
      ' Create permanent QueryDef. 
      Set qdfNew = .CreateQueryDef("NewQueryDef", _ 
         "SELECT * FROM Categories") 
      ' Open Recordset and print report. 
      GetrstTemp qdfNew 
      ' Delete new QueryDef because this is a demonstration. 
      .QueryDefs.Delete qdfNew.Name 
      .Close 
   End With 
 
End Sub 
 
Function GetrstTemp(qdfTemp As QueryDef) 
 
   Dim rstTemp As Recordset 
 
   With qdfTemp 
      Debug.Print .Name 
      Debug.Print "  " & .SQL 
      ' Open Recordset from QueryDef. 
      Set rstTemp = .OpenRecordset(dbOpenSnapshot) 
 
      With rstTemp 
         ' Populate Recordset and print number of records. 
         .MoveLast 
         Debug.Print "  Number of records = " & _ 
            .RecordCount 
         Debug.Print 
         .Close 
      End With 
 
   End With 
 
End Function 

The following example shows how to execute a parameter query. The Parameters collection is used to set the Organization parameter of the myActionQuery query before the query is executed.

Sample code provided by: The Microsoft Access 2010 Programmer’s Reference | About the Contributors

Public Sub ExecParameterQuery()

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef

    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("myActionQuery")

    'Set the value of the QueryDef's parameter
    qdf.Parameters("Organization").Value = "Microsoft"

    'Execute the query
    qdf.Execute dbFailOnError

    'Clean up
    qdf.Close
    Set qdf = Nothing
    Set dbs = Nothing

End Sub

The following example shows how to open a Recordset that is based on a parameter query.

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set dbs = CurrentDb

'Get the parameter query
Set qfd = dbs.QueryDefs("qryMyParameterQuery")

'Supply the parameter value
qdf.Parameters("EnterStartDate") = Date
qdf.Parameters("EnterEndDate") = Date + 7

'Open a Recordset based on the parameter query
Set rst = qdf.OpenRecordset()

About the Contributors

Wrox Press is driven by the Programmer to Programmer philosophy. Wrox books are written by programmers for programmers, and the Wrox brand means authoritative solutions to real-world programming problems.