QueryDef Object, QueryDefs Collection 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