Once you have created a QueryDef, you can modify its properties as easily as modifying any other DAO property.
Public Sub ModifyQuery(strName As String, _
strNewSQL As String, lngPrepare As Long) Dim dbs As Database Dim qdf As QueryDef
Set dbs = CurrentDb
'Modify the QueryDef's properties With dbs.QueryDefs(strName) .SQL = strNewSQL
.Prepare = dbQPrepare 'ODBCDirect workspaces only End With
Set dbs = Nothing End Sub
The QueryDef object's Prepare property is valid only in ODBCDirect workspaces, and specifies if the query is to be prepared on the server as a temporary stored procedure before it is executed.
Setting the Prepare property to dbQPrepare can slow the first execution of the query, but increases performance on all subsequent executions. Some queries, however, can't be executed as stored procedures, in which case, you must specify dbQUnprepare.
Was this article helpful?