>

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:

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