SQL Property

Applies To QueryDef object.

Description

Sets or returns the SQL statement that defines the query executed by a QueryDef object.

Settings and Return Values

The setting or return value is a String that contains an SQL statement.

Remarks

The SQL property contains the SQL statement that determines how records are selected, grouped, and ordered when you execute the query. You can use the query to select records to include in a Recordset object. You can also define action queries to modify data without returning records.

The SQL syntax used in a query must conform to the SQL dialect of the query engine, which is determined by the type of workspace. In a Microsoft Jet workspace, use the Microsoft Jet SQL dialect, unless you create an SQL pass-through query, in which case you should use the dialect of the server. In an ODBCDirect workspace, use the SQL dialect of the server.

Note You can send DAO queries to a variety of different database servers with ODBCDirect, and different servers will recognize slightly different dialects of SQL. Therefore, context-sensitive Help is no longer provided for Microsoft Jet SQL, although online Help for Microsoft Jet SQL is still included through the Help menu. Be sure to check the appropriate reference documentation for the SQL dialect of your database server when using either ODBCDirect connections or pass-through queries in Microsoft Jet – connected client/server applications.

If the SQL statement includes parameters for the query, you must set these before execution. Until you reset the parameters, the same parameter values are applied each time you execute the query.

In an ODBCDirect workspace, you can also use the SQL property to execute a prepared statement on the server. For example, setting the SQL property to the following string will execute a prepared statement named "GetData" with one parameter on a Microsoft SQL Server back-end.

"{call GetData (?)}"

In a Microsoft Jet workspace, using a QueryDef object is the preferred way to perform SQL pass-through operations on Microsoft Jet-connected ODBC data sources. By setting the QueryDef object's Connect property to an ODBC data source, you can use non-Microsoft-Jet-database SQL in the query to be passed to the external server. For example, you can use TRANSACT SQL statements (with Microsoft SQL Server or Sybase SQL Server databases), which the Microsoft Jet database engine would otherwise not process.

Note If you set the property to a string concatenated with a non-integer value, and the system parameters specify a non-U.S. decimal character such as a comma (for example, strSQL = "PRICE > " & lngPrice, and lngPrice = 125,50), an error will result when you try to execute the QueryDef object in a Microsoft Jet database. This is because during concatenation, the number will be converted to a string using your system's default decimal character, and Microsoft Jet SQL only accepts U.S. decimal characters.

See Also CreateQueryDef method, Filter property, OpenRecordset method, Parameter object, Recordset object, Sort 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

Example (Microsoft Access)

The following example creates a parameter query based on an Orders table. The query selects all orders for which the order date falls between the dates entered by the user.

Sub RangeOfOrders()
    Dim dbs As Database, qdf As QueryDef, rst As Recordset

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Create new query.
    Set qdf = dbs.CreateQueryDef("RangeOfOrders")
    ' Construct SQL statement including parameters.
    qdf.SQL = "PARAMETERS [Start] DATETIME, [End] DATETIME; " & _
        "SELECT * FROM Orders WHERE OrderDate BETWEEN " _
        & "[Start] AND [End];"
    qdf.Parameters("Start") = #1/1/96#
    qdf.Parameters("End") = #1/31/96#
    ' Create snapshot-type Recordset object from QueryDef object.
    Set rst = qdf.OpenRecordset(dbOpenSnapshot)
    ' Perform operations with recordset.
        .
        .
        .
    rst.Close
    Set dbs = Nothing
End Sub

Example (Microsoft Excel)

See the CreateQueryDef method example (Microsoft Excel).