>
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