SQL Property Example

This example demonstrates the SQL property by setting and changing the SQL property of a temporary QueryDef and comparing the results. The SQLOutput function is required for this procedure to run.

Sub SQLX()

    Dim dbsNorthwind As Database
    Dim qdfTemp As QueryDef
    Dim rstEmployees As Recordset

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set qdfTemp = dbsNorthwind.CreateQueryDef("")

    ' Open Recordset using temporary QueryDef object and 
    ' print report.
    SQLOutput "SELECT * FROM Employees " & _
        "WHERE Country = 'USA' " & _
        "ORDER BY LastName", qdfTemp

    ' Open Recordset using temporary QueryDef object and 
    ' print report.
    SQLOutput "SELECT * FROM Employees " & _
        "WHERE Country = 'UK' " & _
        "ORDER BY LastName", qdfTemp

    dbsNorthwind.Close

End Sub

Function SQLOutput(strSQL As String, qdfTemp As QueryDef)

    Dim rstEmployees As Recordset

    ' Set SQL property of temporary QueryDef object and open 
    ' a Recordset.
    qdfTemp.SQL = strSQL
    Set rstEmployees = qdfTemp.OpenRecordset

    Debug.Print strSQL

    With rstEmployees
        ' Enumerate Recordset.
        Do While Not .EOF
            Debug.Print "  " & !FirstName & " " & _
                !LastName & ", " & !Country
            .MoveNext
        Loop
        .Close
    End With

End Function