Share via

QueryDef.Prepare Property

  • Article
Access Developer Reference
Bb208511.vs_note(en-us,office.12).gif  Note
ODBCDirect workspaces are not supported in Microsoft Office Access 2007. 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.

Syntax

expression.Prepare

expression   A variable that represents a QueryDef object.

Remarks

You can use the Prepare property to either have the server create a temporary stored procedure from your query and then execute it, or just have the query executed directly. By default the Prepare property is set to dbQPrepare. However, you can set this property to dbQUnprepare to prohibit preparing of the query. In this case, the query is executed using the SQLExecDirect API.

Creating a stored procedure can slow down the initial operation, but increases performance of all subsequent references to the query. However, some queries cannot be executed in the form of stored procedures. In these cases, you must set the Prepare property to dbQUnprepare.

If Prepare is set to dbQPrepare, this can be overridden when the query is executed by setting the Execute method's options argument to dbExecDirect.

Bb208511.vs_note(en-us,office.12).gif  Note
The ODBC SQLPrepare API is called as soon as the DAO SQL property is set. Therefore, if you want to improve performance using the dbQUnprepare option, you must set the Prepare property before setting the SQL property.

Example

This example uses the Prepare property to specify that a query should be executed directly rather than first creating a temporary stored procedure on the server.

Visual Basic for Applications 
  Sub PrepareX()

Dim wrkODBC As Workspace
Dim conPubs As Connection
Dim qdfTemp As QueryDef
Dim rstTemp As Recordset


' Create ODBCDirect Workspace object and open Connection
' object.
Set wrkODBC = CreateWorkspace("", _
"admin", "", dbUseODBC)


' Note: The DSN referenced below must be configured to
'       use Microsoft Windows NT Authentication Mode to
'       authorize user access to the Microsoft SQL Server.
Set conPubs = wrkODBC.OpenConnection("Publishers", , , _
"ODBC;DATABASE=pubs;DSN=Publishers")


Set qdfTemp = conPubs.CreateQueryDef("")


With qdfTemp
' Because you will only run this query once, specify
' the ODBC SQLExecDirect API function. If you do
' not set this property before you set the SQL
' property, the ODBC SQLPrepare API function will
' be called anyway which will nullify any
' performance gain.
.Prepare = dbQUnprepare
.SQL = "UPDATE roysched " & _
"SET royalty = royalty * 2 " & _
"WHERE title_id LIKE 'BU____' OR " & _
"title_id LIKE 'PC____'"
.Execute
End With


Debug.Print "Query results:"


' Open recordset containing modified records.
Set rstTemp = conPubs.OpenRecordset( _
"SELECT * FROM roysched " & _
"WHERE title_id LIKE 'BU____' OR " & _
"title_id LIKE 'PC____'")


' Enumerate recordset.
With rstTemp
Do While Not .EOF
Debug.Print , !title_id, !lorange, _
!hirange, !royalty
.MoveNext
Loop
.Close
End With


conPubs.Close
wrkODBC.Close