About batch transactions

In a Microsoft Access project, you can ensure data integrity for insert, update, and delete operations on multiple records by creating a bound form that uses a batch transaction.

What is a batch transaction?

A batch transaction guarantees that information in the database is logically consistent at all times, even when a single logical operation contains multiple database operations. Access and SQL Server treat all database changes inside a transaction as a single unit of work. By definition, either an entire transaction is completed safely and all resulting changes are reflected in the database, or the transaction is rolled backùand all changes to the database are undone. Consistency and recoverability of a database transaction are guaranteed even in the case of system failure in the middle of complex updates by more than one user.

Without a batch transaction, each record is saved to the database independently, which can make it very difficult to ensure data integrity for a single logical operation. With a batch transaction, you can ensure one of two things: either all changes are made or no changes are made to the server database for a single logical operation.

For example, without a batch transaction, a user entering a new order on a form in the Northwind database saves the order information, and then each line item one at a time. A second user running a report while an order is partially complete will see incorrect order information, such as an order with no line items or one line item instead of two. With a batch transaction, the second user will only see a report that contains either the complete order information or none of the order information.

When you update a form in a batch transaction, three things can occur:

• Commit   After making changes to several records, you save all the records and they are successfully updated to the database server. All updated records are resynchronized to retrieve calculated fields, default values, and other server-generated information. The record position, sort order, and filter remain the same.
• Rollback   After making changes to several records, you attempt to save all records, but the batch transaction fails. The database server returns an error on one or more of the records, such as a constraint or locking violation. However, all pending data changes on the form remain so you can correct the error and re-save the records without having to re-enter all the changes.
• Undo All Records   After making changes to several records, you click Undo All Records on the Records menu. Access ignores any changes and returns the form and data to its original state before the batch transaction began. The database server never sees any changes.

Enabling a batch transaction

The following are requirements for creating and using batch transactions in Microsoft Access:

• You must be using an Access project connected to a Microsoft SQL Server database (versions 2000, 7.0, and 6.5).
• You can only use a data-entry form, and the form must be bound to an updateable record source (inline user-defined function, single statement stored procedure, or SQL SELECT statement). The form can have subforms, but you cannot enclose two independent forms in a single batch transaction.

• You set the Batch Updates property of the form to Yes (the default value is No). In a main form/subform combination, this property applies only to the main form. Subforms that are linked to the main form participate in the transaction regardless of the subform's Batch Update property setting. If a subform is not linked to the main form, then it won't participate in the batch transaction.

Changing the default behavior when you save records in a batch transaction

By default, Access attempts to commit records when a user navigates off a master record (in a main form/subform combination), closes the form, or clicks Save All Records on the Records menu.

However you can use two additional properties to change the Access default behavior for batch transactions:

• Using the Commit On Close property, you can either make Access discard changes when the user closes a form or prompt the user to decide whether or not to save the records.

• Using the Commit On Navigation property on the main form of a form/sub-form combination, you can set the value to Yes to commit one form's worth of data at a time.

If you set the Batch Updates property to Yes, by default, the value of Commit On Close is set to Yes and Commit On Navigation is set to No. This means a user can commit only when the form or main form is closed, or a user clicks the Save All Records command on the Records menu. A user won't commit when navigating off the master record unless the value of Commit On Navigation is Yes.

If you set both the Commit On Close property and Commit on Navigate property to No, this means that the user can close the form and lose any updated records without the default warning message. In this case, a developer should add a "Save All Records" button to the form so that the user can save changes, or instruct the user to use the Save All Records command on the Records menu.