Executing Queries

There are three ways to programmatically execute a query: using the DoCmd.RunSQL method, the object .Execute method, and the OpenRecordset method. Not all of these methods return records. The query argument for any of the following methods can either be the name of a permanent or temporary QueryDef, or a string expression that equates to a query:

DoCmd.RunSQL

Although not part of the DAO object model, you can execute the RunSQL method of the DoCmd object to run an action query:

DoCmd.RunSQL "UPDATE Table1 SET Field1 = 123"

DoCmd.RunSQL qryMyQuery

Running a query in this way will display a message box to confirm that you wish to make changes to the database. To eliminate this message box, set the DoCmd object's SetWarnings property to False prior to calling DoCmd.RunSQL, but remember to set it back when you've finished, otherwise all warning messages will thereafter be disabled.

DoCmd.SetWarnings False DoCmd.RunSQL qryMyQuery DoCmd.SetWarnings True

Any errors raised while executing the query will display a message box. You can disable the message box as described above, and you can trap the error using the On Error Goto construct. By default, the query will be included in an existing transaction, but you can exclude it by setting the UseTransaction property to False.

DoCmd.RunSQL qryMyQuery, False object.Execute

You can use the Execute method of the QueryDef object, Microsoft Jet or ODBCDirect Database object, and ODBCDirect Connection object, to run an action query:

qdf.Execute options

dbs.Execute qryMyQuery,

options

cn.Execute qryMyQuery,

options

With the Execute method, there is no need to set the SetWarnings property to disable change confirmation message boxes, because none are displayed. The Execute method operates directly on its parent object.

There are several major benefits to using the Execute method in preference to the DoCmd.RunSQL method:

□ The Execute method runs faster than DoCmd.RunSQL.

□ The Execute method can be included in an existing transaction, like any other DAO operation, without needing to specify an option to do so.

□ You can specify several options that change the way the method works.

The following table lists the various constants that can be supplied as options for the Execute method.

Constant

Description

DbDenyWrite

Denies write permission to other users (Microsoft Jet workspaces only).

DbInconsistent

Executes inconsistent updates (Microsoft Jet workspaces only).

DbConsistent

Executes consistent updates (Microsoft Jet workspaces only).

dbSQLPassThrough

Executes an SQL pass-through query, which passes the query to an ODBC database for processing. (Microsoft Jet workspaces only).

DbFailOnError

Rolls back updates if an error occurs (Microsoft Jet workspaces only).

DbSeeChanges

Generates a runtime error if another user is changing data that you are editing (Microsoft Jet workspaces only).

DbRunAsync

Executes the query asynchronously (ODBC Connection and QueryDef objects only). This allows your code to keep running while the query is loading.

In ODBCDirect workspaces, you can use the StillExecuting property to determine if the query has completed.

DbExecDirect

Executes the statement without first calling the SQLPrepare ODBC API function (ODBCDirect Connection and QueryDef objects only).

You can execute a query when you open a recordset. To do so, specify the query name in the Database object's OpenRecordset method to run a select or action query:

dbs.OpenRecordset(qryMyQuery)

Similarly, you can open a recordset based on a query, like so:

Set qdf = dbs.QueryDefs("qryMyQuery") Set rst = qdf.OpenRecordset(dbOpenDynaset)

The following section on recordsets describes this in greater detail.

0 0

Post a comment