Transactions in Access and SQL Server

Microsoft® Access and Microsoft SQL Server™ share two types of transactions.

• Explicit transactions

  Explicitly start a transaction by issuing a BEGIN TRANSACTION statement.

• Autocommit transactions

  This is the default mode for both Access and SQL Server. Each individual Transact-SQL statement is committed when it completes. You do not have to specify any statements to control transactions.

SQL Server also offers a third type of transaction.

• Implicit transactions

  An implicit transaction is started whenever an INSERT, UPDATE, DELETE, or other data manipulating function is performed. To allow implicit transactions, use the SET IMPLICIT_TRANSACTIONS ON statement.

If you are issuing a script through SQL Server Query Analyzer or other query tools, you can either include the explicit BEGIN TRANSACTION statement, or start the script with the SET IMPLICIT_TRANSACTIONS ON statement.

SQL Server also offers the SAVE TRANSACTION statement. It sets a savepoint in the transaction, allowing partial rollbacks.

See Also