Execute Method

Applies To

Database Object, QueryDef Object.

Description

Runs an action query or executes an SQL statement on a specified Database object.

Syntax

database.Execute source[, options]

querydef.Execute [options]

For a Database object, the Execute method syntax has these parts.

Part	Description
database	The name of the Database object on which the query will run.
source	The SQL statement or QueryDef object.
options	An integer constant that determines the data integrity characteristics of the query, as specified in Settings.

For a QueryDef object, the Execute method syntax has these parts.

Part	Description
querydef	The name of the QueryDef object whose SQL property setting specifies the SQL statement to execute.
options	An integer constant that determines the data integrity characteristics of the query, as specified in Settings.

Settings

You can use the following constants for the options part.

Constant	Description
dbDenyWrite	Deny write permission to other users.
dbInconsistent	(Default) Inconsistent updates.
dbConsistent	Consistent updates.
dbSQLPassThrough	SQL pass-through. Causes the SQL statement to be passed to an ODBC database for processing.
dbFailOnError	Rolls back updates if an error occurs.
dbSeeChanges	Generates a run-time error if another user is changing data you are editing.

Remarks

The Execute method is valid only for action queries. If you use Execute with another type of query, an error occurs. Because an action query doesn't return any records, Execute doesn't return a recordset.

Tips

Given a syntactically correct SQL statement and proper permissions, the Execute method won't fail — even if not a single row can be modified or deleted. Therefore, always use the dbFailOnError option when using the Execute method to run an update or delete query. This option generates a trappable error and rolls back all successful changes if any of the records affected are locked and can't be updated or deleted.
For best performance, especially in a multiuser environment, nest the Execute method inside a transaction: Use the BeginTrans method on the current Workspace object, then use the Execute method, and complete the transaction by using the CommitTrans method on the Workspace. This saves changes on disk and frees any locks placed while the query was running.

Use the RecordsAffected property of the Database or QueryDef object to determine the number of records affected by the most recent Execute method. For example, RecordsAffected contains the number of records deleted, updated, or inserted when executing an action query. When you use the Execute method to run a Querydef, the RecordsAffected property of the QueryDef object is set to the number of records affected.

If both dbInconsistent and dbConsistent are included or if neither is included, the result is the default.

Using Execute on a query that selects records produces an error.

Execute doesn't return a recordset.

See Also

RecordsAffected Property.

Example

This example uses a Data control to execute an action query that updates all titles that have no value in the ISBN column in the Titles table. If there is an error, all changes are rolled back.

Dim strSQL as String
strSQL = "DELETE FROM Titles WHERE ISBN IS NULL"
dbsBiblio.Execute strSQL, dbFailOnError

Example (Microsoft Access)

The following example executes an action query and prints the number of records affected.

Sub RecordsUpdated()
    Dim dbs As Database, qdf As QueryDef
    Dim strSQL As String
    
    ' Return Database variable pointing to current database.
    Set dbs = CurrentDb
    strSQL = "UPDATE Employees SET Title = " & _
        "'Senior Sales Representative' " & _
            "WHERE Title = 'Sales Representative';"
    ' Create new QueryDef.
    Set qdf = dbs.CreateQueryDef("UpdateTitles", strSQL)
    ' Execute QueryDef.
    qdf.Execute
    Debug.Print qdf.RecordsAffected
End Sub