QueryDef Object

Description

A QueryDef object is a stored definition of a query in a Microsoft Jet database, or a temporary definition of a query in an ODBCDirect workspace.

Remarks

You can use the QueryDef object to define a query. For example, you can:

Use the SQL property to set or return the query definition.
Use the QueryDef object's Parameters collection to set or return query parameters.
Use the Type property to return a value indicating whether the query selects records from an existing table, makes a new table, inserts records from one table into another table, deletes records, or updates records.
Use the MaxRecords property to limit the number of records returned from a query.
Use the ODBCTimeout property to indicate how long to wait before the query returns records. The ODBCTimeout property applies to any query that accesses ODBC data.

In a Microsoft Jet workspace, you can also:

Use the ReturnsRecords property to indicate that the query returns records. The ReturnsRecords property is only valid on SQL pass-through queries.
Use the Connect property to make an SQL pass-through query to an ODBC database.

In an ODBCDirect workspace, you can also:

Use the Prepare property to determine whether to invoke the ODBC SQLPrepare API when the query is executed.
Use the CacheSize property to cache records returned from a query.

You can also create temporary QueryDef objects. Unlike permanent QueryDef objects, temporary QueryDef objects are not saved to disk or appended to the QueryDefs collection. Temporary QueryDef objects are useful for queries that you must run repeatedly during run time but do not need to save to disk, particularly if you create their SQL statements during run time.

You can think of a permanent QueryDef object in a Microsoft Jet workspaces as a compiled SQL statement. If you execute a query from a permanent QueryDef object, the query will run faster than if you run the equivalent SQL statement from the OpenRecordset method. This is because the Microsoft Jet database engine doesn't need to compile the query before executing it.

The preferred way to use the native SQL dialect of an external database engine accessed through the Microsoft Jet database engine is through QueryDef objects. For example, you can create a Microsoft SQL Server query and store it in a QueryDef object. When you need to use a non-Microsoft Jet database engine SQL query, you must provide a Connect property string that points to the external data source. Queries with valid Connect properties bypass the Microsoft Jet database engine and pass the query directly to the external database server for processing.

To create a new QueryDef object, use the CreateQueryDef method. In a Microsoft Jet workspace, if you supply a string for the name argument or if you explicitly set the Name property of the new QueryDef object to a non-zero-length string, you will create a permanent QueryDef that will automatically be appended to the QueryDefs collection and saved to disk. Supplying a zero-length string as the name argument or explicitly setting the Name property to a zero-length string will result in a temporary QueryDef object.

In an ODBCDirect workspace, a QueryDef is always temporary. The QueryDefs collection contains all open QueryDef objects. When a QueryDef is closed, it is automatically removed from the QueryDefs collection.

To refer to a QueryDef object in a collection by its ordinal number or by its Name property setting, use any of the following syntax forms:

QueryDefs(0)

QueryDefs("name")

QueryDefs![name]

You can refer to temporary QueryDef objects only by the object variables that you have assigned to them.

Properties

CacheSize property, Connect property, DateCreated, LastUpdated properties, KeepLocal property, LogMessages property, MaxRecords property, Name property, ODBCTimeout property, Prepare property, RecordsAffected property, Replicable property, ReturnsRecords property, SQL property, StillExecuting property, Type property, Updatable property.

Methods

Cancel method, Close method, CreateProperty method, Execute method, OpenRecordset method.

See Also CreateQueryDef method.

Specifics (Microsoft Access)

In addition to the properties defined by the Microsoft Jet database engine, a QueryDef object may also contain these Microsoft Access application–defined properties. For details on checking and setting these properties, see the individual properties and the Property object.

DatasheetFontHeight	DatasheetFontItalic
DatasheetFontName	DatasheetFontUnderline
DatasheetFontWeight	Description
FrozenColumns	LogMessages
RecordLocks	RowHeight
ShowGrid	UseTransaction

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

Example (Microsoft Access)

The following example checks to see if there is a query called RecentHires in the current database, and deletes it from the QueryDefs collection if it exists. Then the procedure creates a new QueryDef object and opens it in Datasheet view.

Sub NewQuery()
    Dim dbs As Database, qdf As QueryDef
    Dim strSQL As String

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Refresh QueryDefs collection.
    dbs.QueryDefs.Refresh
    ' If RecentHires query exists, delete it.
    For Each qdf in dbs.QueryDefs
        If qdf.Name = "RecentHires" Then
            dbs.QueryDefs.Delete qdf.Name
        End If
    Next qdf
    ' Create SQL string to select employees hired on or after 1-1-94.
    strSQL = "SELECT * FROM Employees WHERE HireDate >= #1-1-94#;"
    ' Create new QueryDef object.
    Set qdf = dbs.CreateQueryDef("RecentHires", strSQL)
    ' Open query in Datasheet view.
    DoCmd.OpenQuery qdf.Name
    Set dbs = Nothing
End Sub