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 expression that contains an SQL statement. The data type is String.

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 dynaset- or snapshot-type Recordset object. You can also define bulk queries to modify data without returning records.

Using the ORDER BY or WHERE clause to sort or filter a Recordset object is more efficient than using the Sort and Filter properties.

The SQL syntax used in a query must conform to the SQL dialect as defined by the Microsoft Jet database engine unless you create an SQL pass through query.

Using a QueryDef object is the preferred way to perform SQL pass through operations on ODBC databases. By setting the QueryDef object's Connect property to an ODBC data source, you can specify non-Microsoft Jet database SQL in the query passed to the external server. For example, you can use TRANSACT SQL statements (with Microsoft SQL Server or Sybase SQL Server databases) in the value argument, which won't be processed by the Jet database engine.

If the SQL statement includes parameters for the query, you must set these before you execute the query. Until you reset the parameters, the same parameter values are applied each time you execute the query. Set the parameters by accessing the Parameters collection of the QueryDef object using the following code.

qdfOrders.Parameters("Order Date") = "10/11/94"   ' Set parameters.
qdfOrders.Parameters("Ship Date") = "11/4/94"

See Also

CreateQueryDef Method, Filter Property, OpenRecordset Method, Parameter Object, Recordset Object, Sort Property.

Example

This example creates a query based on an Orders table. The query selects all orders that have a Freight value greater than 10.

Dim dbsNorthwind As Database, qdfLargeFrt As QueryDef
Dim rstFromQuery As Recordset
Set dbsNorthwind =  DBEngine.Workspaces(0).OpenDatabase("Northwind.mdb")
Set qdfLargeFrt = dbsNorthwind.CreateQueryDef("Large Freight")
qdfLargeFrt.SQL = "SELECT * FROM Orders WHERE Freight > 10;"
Set rstFromQuery = qdfLargeFrt.OpenRecordset(dbOpenSnapshot)

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 Database variable that points to current database.
    Set dbs = CurrentDb
    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/95#
    qdf.Parameters("End") = #1/31/95#
    ' Create snapshot-type Recordset object from QueryDef object.
    Set rst = qdf.OpenRecordset(dbOpenSnapshot)
End Sub

Example (Microsoft Excel)

See the CreateQueryDef method example.