QueryDef Object

Description

A QueryDef object is a stored definition of a query in a Microsoft Jet database.

Remarks

The QueryDef object corresponds to a stored query definition in a database. You can think of a saved query as a compiled SQL statement.

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

Check and modify the SQL property setting, set its parameters, and then run the query.
Set query parameters using the QueryDef object's Parameters collection.
Set or check the Type property to determine 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.
Retrieve data from an ODBC data source by setting the ODBCTimeout and Connect properties and, if the query isn't a select query, setting the ReturnsRecords property to False.
Indicate the query is to be passed to an external ODBC server by setting the Connect property, making it an SQL pass through query.
Use the ReturnsRecords property to indicate that the query returns records. The ReturnsRecords property is only valid on SQL pass through queries.
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.

Queries executed from QueryDef objects run faster than queries specified by the OpenRecordset method 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 is by using QueryDef objects. For example, you can create a Transact SQL query (as used with Microsoft SQL Server) and store it in a QueryDef object. When you need to use a non-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 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.

Properties

Connect Property; DateCreated, LastUpdated Properties; KeepLocal Property; LogMessages Property; Name Property; ODBCTimeout Property; RecordsAffected Property; Replicable Property; ReturnsRecords Property; SQL Property; Type Property; Updatable Property.

Methods

CreateProperty Method, Execute Method, OpenRecordset Method.

See Also

CreateQueryDef Method; Appendix, "Data Access Object Hierarchy."

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 topics for the individual properties and the Property object.

DatasheetFontHeight	FrozenColumns
DatasheetFontItalic	LogMessages
DatasheetFontName	RecordLocks
DatasheetFontUnderline	RowHeight
DatasheetFontWeight	ShowGrid
Description

Example

This example creates a new QueryDef object and appends it to the QueryDefs collection in the current database. Then the example enumerates all the QueryDef objects in the database and all the properties of the new QueryDef.

Function EnumerateQueryDef () As Integer
    Dim wrkCurrent As Workspace, dbsExample As Database, qdfTest As _ 
        QueryDef
    Dim I As Integer
    Set wrkCurrent = DBEngine.Workspaces(0)
    Set dbsExample = wrkCurrent.OpenDatabase("Northwind.mdb")
    Set qdfTest = dbsExample.CreateQueryDef("This is a test")
    Debug.Print
    ' Enumerate QueryDef objects.
    Debug.Print
    For I = 0 To dbsExample.QueryDefs.Count - 1
        Debug.Print dbsExample.QueryDefs(I).Name
    Next I
    ' Enumerate built-in properties of qdfTest.
    Debug.Print
    Debug.Print "qdfTest.Name: "; qdfTest.Name
    Debug.Print "qdfTest.DateCreated: "; qdfTest.DateCreated
    Debug.Print "qdfTest.LastUpdated: "; qdfTest.LastUpdated
    Debug.Print "qdfTest.SQL: "; qdfTest.SQL
    Debug.Print "qdfTest.ODBCTimeout: "; qdfTest.ODBCTimeout
    Debug.Print "qdfTest.Updatable: "; qdfTest.Updatable
    Debug.Print "qdfTest.Type: "; qdfTest.Type
    Debug.Print "qdfTest.Connect: "; qdfTest.Connect
    Debug.Print "qdfTest.ReturnsRecords: "; qdfTest.ReturnsRecords
    dbsExample.QueryDefs.Delete "This is a test"
    EnumerateQueryDef = True
End Function

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 Database object pointing 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 after 1-1-95.
    strSQL = "SELECT * FROM Employees WHERE HireDate >= #1-1-95#;"
    ' Create new QueryDef object.
    Set qdf = dbs.CreateQueryDef("RecentHires", strSQL)
    ' Open query in Datasheet view.
    DoCmd.OpenQuery qdf.Name
End Sub