Set qdfChange = dbsNorthwind.CreateQueryDef("", _ Set dbsNorthwind = OpenDatabase("Northwind.mdb") StrSQLRestore = "UPDATE Employees SET Country = " & _ StrSQLChange = "UPDATE Employees SET Country = " & _ ' Define two SQL statements for action queries. The ExecuteQueryDef and PrintOutput procedures are required for this procedure to run. This example demonstrates the Execute method when run from both a QueryDef object and a Database object. This saves changes on disk and frees any locks placed while the query is running. 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. If you are updating older DAO code, be sure to consider using explicit transactions around Execute statements.įor best performance in a Microsoft Access workspace, especially in a multiuser environment, nest the Execute method inside a transaction. To improve performance, these implicit transactions were removed starting with version 3.5. If part of a statement executed with dbFailOnError failed, the entire statement would be rolled back. In earlier versions of the Microsoft Jet database engine, SQL statements were automatically embedded in implicit transactions. This option generates a run-time error and rolls back all successful changes if any of the records affected are locked and can't be updated or deleted.
MS ACCESS DELETE QUERY UPDATE
Therefore, always use the dbFailOnError option when using the Execute method to run an update or delete query. In a Microsoft Access workspace, if you provide a syntactically correct SQL statement and have the appropriate permissions, the Execute method won't fail - even if not a single row can be modified or deleted. When you use the Execute method to run a query, the RecordsAffected property of the QueryDef object is set to the number of records affected. For example, RecordsAffected contains the number of records deleted, updated, or inserted when executing an action query. Use the RecordsAffected property of the Connection, Database, or QueryDef object to determine the number of records affected by the most recent Execute method. Using both dbConsistent and dbInconsistent causes an error.
You can use one or the other, but not both in a given instance of OpenRecordset. The constants dbConsistent and dbInconsistent are mutually exclusive. Generates a run-time error if another user is changing data you are editing (Microsoft Access workspaces only).Įxecutes the query asynchronously (ODBCDirect Connection and QueryDef objects only).Įxecutes the statement without first calling SQLPrepare ODBC API function (ODBCDirect Connection and QueryDef objects only). Rolls back updates if an error occurs (Microsoft Access workspaces only). Setting this option passes the SQL statement to an ODBC database for processing (Microsoft Access workspaces only). (Default) Executes inconsistent updates (Microsoft Access workspaces only).Įxecutes consistent updates (Microsoft Access workspaces only).Įxecutes an SQL pass-through query. You can use the following RecordsetOptionEnum constants for Options.ĭenies write permission to other users (Microsoft Access workspaces only). SyntaxĮxpression A variable that represents a QueryDef object. Executes an SQL statement on the specified object.