A QueryDef object is a stored definition of a query in a Microsoft Access database engine database.
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.
- 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 ODC database.
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 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 Access workspace 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 Access 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 Access 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 Access 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 Access 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 Access 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.
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.
Link provided by the
UtterAccess community. UtterAccess is the premier Microsoft Access wiki and help forum.
Methods
Name
| Description
|
|---|
|
| NOTE: ODBCDirect workspaces are not supported in Microsoft Access 2013. Use ADO if you want to access external data sources without using the Microsoft Access database engine.
Cancels execution of a pending asynchronous method call (ODBCDirect workspaces only).
|
|
| Closes an open QueryDef.
|
|
| Creates a new user-defined Property object (Microsoft Access workspaces only). |
|
| Executes an SQL statement on the specified object.
|
|
| Creates a new Recordset object and appends it to the Recordsets collection. |
Properties
Name
| Description
|
|---|
|
| Sets or returns the number of records retrieved from an ODBC data source that will be cached locally. Read/write Long.
|
|
| Sets or returns a value that provides information about the source of database used in a pass-through query. Read-only String.
|
|
| Returns the date and time that an object was created (Microsoft Access workspaces only). Read-only Variant.
|
|
| Returns a Fields collection that represents all stored Field objects for the specified object. Read-only. |
|
| Returns the date and time of the most recent change made to an object. Read-only Variant.
|
|
| Sets or returns the maximum number of records to return from a query against an ODBC data source.
|
|
| Returns or sets the name of the specified object. Read/write String.
|
|
| Indicates the number of seconds to wait before a timeout error occurs when a QueryDef is executed on an ODBC database. |
|
| Returns a Parameters collection that contains all of the Parameter objects of the specified QueryDef. Read-only. |
|
| NOTE: ODBCDirect workspaces are not supported in Microsoft Access 2013. Use ADO if you want to access external data sources without using the Microsoft Access database engine.
Sets or returns a value that indicates whether the query should be prepared on the server as a temporary stored procedure, using the ODBC SQLPrepare API function, prior to execution, or just executed using the ODBC SQLExecDirect API function (ODBCDirect workspaces only). Read/Write QueryDefStateEnum. |
|
| Returns the Properties collection of the specified object. Read-only. |
|
| Returns the number of records affected by the most recently invoked Execute method. |
|
| Sets or returns a value that indicates whether an SQL pass-through query to an external database returns records (Microsoft Access workspaces only).
|
|
| Sets or returns the SQL statement that defines the query executed by a QueryDef object. |
|
| NOTE: ODBCDirect workspaces are not supported in Microsoft Access 2013. Use ADO if you want to access external data sources without using the Microsoft Access database engine.
Indicates whether or not an asynchronous operation (that is, a method called with the dbRunAsync option) has finished executing (ODBCDirect workspaces only). |
|
| Sets or returns a value that indicates the operational type or data type of an object. Read-onlyInteger.
|
|
| Returns a value that indicates whether you can change a DAO object. Read-only Boolean.
|
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
This example uses the CreateQueryDef method to create and execute both a temporary and a permanent QueryDef. The GetrstTemp function is required for this procedure to run.
Sub CreateQueryDefX()
Dim dbsNorthwind As Database
Dim qdfTemp As QueryDef
Dim qdfNew As QueryDef
Set dbsNorthwind = OpenDatabase("Northwind.mdb")
With dbsNorthwind
' Create temporary QueryDef.
Set qdfTemp = .CreateQueryDef("", _
"SELECT * FROM Employees")
' Open Recordset and print report.
GetrstTemp qdfTemp
' Create permanent QueryDef.
Set qdfNew = .CreateQueryDef("NewQueryDef", _
"SELECT * FROM Categories")
' Open Recordset and print report.
GetrstTemp qdfNew
' Delete new QueryDef because this is a demonstration.
.QueryDefs.Delete qdfNew.Name
.Close
End With
End Sub
Function GetrstTemp(qdfTemp As QueryDef)
Dim rstTemp As Recordset
With qdfTemp
Debug.Print .Name
Debug.Print " " & .SQL
' Open Recordset from QueryDef.
Set rstTemp = .OpenRecordset(dbOpenSnapshot)
With rstTemp
' Populate Recordset and print number of records.
.MoveLast
Debug.Print " Number of records = " & _
.RecordCount
Debug.Print
.Close
End With
End With
End Function
The following example shows how to replace the Structured Query Language (SQL) statement in a saved query.
‘To change the Where clause in a saved query
Dim qdf as QueryDef
Dim db as Database
Set db = CurrentDB
Set qdf = db.QueryDefs("YourQueryName")
qdf.SQL = ReplaceWhereClause(qdf.SQL, strYourNewWhereClause)
set qdf = Nothing
set db = Nothing
Public Function ReplaceWhereClause(strSQL As Variant, strNewWHERE As Variant)
On Error GoTo Error_Handler
‘This subroutine accepts a valid SQL string and Where clause, and
‘returns the same SQL statement with the original Where clause (if any)
‘replaced by the passed in Where clause.
‘
‘INPUT:
‘ strSQL valid SQL string to change
‘OUTPUT:
‘ strNewWHERE New WHERE clause to insert into SQL statement
‘
Dim strSELECT As String, strWhere As String
Dim strOrderBy As String, strGROUPBY As String, strHAVING As String
Call ParseSQL(strSQL, strSELECT, strWhere, strOrderBy, _
strGROUPBY, strHAVING)
ReplaceWhereClause = strSELECT &""& strNewWHERE &""_
& strGROUPBY &""& strHAVING &""& strOrderBy
Exit_Procedure:
Exit Function
Error_Handler:
MsgBox (Err.Number & ": " & Err.Description)
Resume Exit_Procedure
End Function