Only the Microsoft Access driver supports the use of transactions. The default transaction mechanism is auto-commit, in which each statement that can be contained in a transaction is, in effect, in its own transaction. A transaction is implicitly started when such an SQL statement is executed; upon completion of the statement, it’s committed automatically.
When a transaction is explicitly started (by calling SQLSetConnectOption with the SQL_AUTOCOMMIT fOption argument set to SQL_AUTOCOMMIT_OFF), the SQLTransact function is called to commit or roll back the transaction. The Microsoft Access driver supports multiple active statement handles on a single connection, so when SQLTransact is called, all statements on the connection are either committed or rolled back.
All open cursors on all hstmt arguments associated with the hdbc argument are closed when the transaction is committed or rolled back. SQLTransact leaves any hstmt argument present in a prepared state if the statement was prepared, or in an allocated state if it was executed directly. Closing all cursors can have unforeseen consequences. For example, suppose an application has two active statements within an explicit transaction: one statement in which an UPDATE statement was executed, and another statement in which a SELECT statement was executed and then SQLExtendedFetch called to return a recordset. If SQLTransact is called to commit the update, all the operations performed by the UPDATE statement on the first statement handle are committed (as expected), but in addition, the recordset generated by SQLExtendedFetch is deleted, because the cursor on the second statement handle is closed.
The Microsoft Access driver supports multiple active transactions. Because transactions are associated with a connection in ODBC, each transaction must be on a different connection (hdbc argument). Because nested transactions, which are supported natively by the Microsoft Jet engine, are not supported in ODBC, they are not available through the Desktop Database Drivers.
The driver supports only the read-committed transaction isolation level. Transactions can contain data definition language (DDL) statements and data manipulation language (DML) statements in any order.