Although the concept of transactions is the same in Microsoft® Access and Microsoft SQL Server™, the reason for using transactions varies. One reason to use transactions in Access is to keep a query plan in memory. If you want to make 10 updates to a table using the same format, after the first update is made, the subsequent nine updates wrapped in the same transaction use the same optimized query structure. In SQL Server, a stored procedure, even a temporary one, holds the query structure automatically in its procedure cache. More importantly, a single wrapped transaction can fail if two updates occur to the same record or on the same datapage.
Access and SQL Server also accommodate transactions to achieve atomicity, the linking together of two or more statements so that they either both succeed or both fail. The classic example uses a banking situation. If you are transferring money from a checking account to a savings account, you want both transactions to either succeed or fail. This avoids the problem of moving the money out of checking but failing to deposit it into savings.
With Access, if you experience a power failure, you may be left with an incomplete transaction upon restart. SQL Server’s transaction log prevents this. Upon startup after a power failure, the transaction log rolls the database activity forward to a stable condition. Transactions, if completed in the log, are applied to the data pages. Incomplete transactions are rolled back. This is an advantage even if you do not use transactions in your code. Normal database activity produces transactions, such as page splits, that are required when a datapage is full. Such transactions are completed or rolled back, leaving your database in an internally consistent state.