Although using transactions can improve the functionality and performance of your client/server applications, they have several limitations.
Very few servers support nested transactions (for example, SQL Server and Oracle do not). Because of this, Microsoft Jet doesn't support any transaction nesting against ODBC data and will ignore attempts to nest transactions. This is a common code change to make when porting code that runs against MDB data sources to ODBC data sources.
Some pass-through queries may not be allowed within a transaction. For example, Microsoft SQL Server doesn't allow data-definition, permission, or backup-related statements within transactions. Check your server's documentation to determine these limits.
Microsoft Jet often conserves connections by sharing them between queries. If you use server-specific transaction commands in pass-through queries, they can confuse the internal tracking of server transactions performed by Jet as well as providing you with unexpected results. For example, on SQL Server don't use the SQL Server command "BEGIN TRAN". Instead, use the BeginTrans method. Jet translates these transaction methods into the equivalent server commands.
Locks get placed on the server for every operation you perform inside a transaction. Be aware of the number of locks you're requesting and make sure your server is configured to handle it.
Remote data used within the Workspace objects does not have an isolated transaction space. For example, if you use two Workspace objects to open tables attached to an ODBC data source, transaction methods on one Workspace will affect transactions on the other. Since its unusual to have two different sets of transactions used concurrently, this is not likely to affect much code.
If you want to have multiple concurrent transactions on your server, you can force each workspace to have a distinct remote transaction space by setting the IsolateODBCTrans property of the Workspace object to True. This prevents the workspace from sharing connections with other workspaces, thus guaranteeing transaction isolation. For details on the IsolateODBCTrans property, search the Microsoft Access Readme Help for "IsolateODBCTrans."