CreateQueryDef Method Example (MDB)

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