When your DAO code does not use transaction methods explicitly, Microsoft Jet will use ODBC in its "auto-commit" mode. In this mode, each SQL statement sent to the server has an implicit transaction around it – that is, the SQL statement has an immediate effect on the ODBC data and cannot be rolled back. Jet will stop using auto-commit mode off and use explicit ODBC transactions in two scenarios:
When using so-called "bulk operation" SQL statements such as UPDATE, INSERT and APPEND, Jet will place a transaction around the operation so that it can succeed or fail as a single entity. This is necessary because one such Jet SQL statement may actually correspond to many individual server statements.
Multiple concurrent transactions against recordsets against a single server are actually a single transaction because a single connection is being used to service updates for both dynasets. You should structure your transactions so that they do not overlap; transactions are intended to be atomic units.
If the server supports transactions at all, Jet assumes only single-level support; that is, no nesting of transactions. Therefore, if your Basic code nests transactions, only the outermost BeginTrans, Commit, and Rollback are actually sent to the server, and nested transaction methods are silently ignored.