Although transactions can improve the functionality and performance of your client/server applications, they have several limitations, as described in the following sections.
Although Microsoft Jet supports nesting transactions on its own native data sources, ODBC doesn’t support nesting transactions. 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 you need to make when converting code that runs against local data sources to work with ODBC data sources.
Some queries may not be allowed within a transaction. For example, 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 Microsoft Jet, as well as provide you with unexpected results. For example, on SQL Server, don’t use the SQL Server command BEGIN TRAN. Instead, use the BeginTrans method. Microsoft Jet translates DAO transaction methods into the equivalent server commands.
Locks may be 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 them.
Note You can control the maximum number of locks Microsoft Jet places against a shared database using the MaxLocksPerFile registry setting. For more information, see “Setting the Maximum Number of Locks” in Chapter 13, “Optimizing Performance.”
For efficiency reasons, remote data used within Workspace objects does not have an isolated transaction space. For example, if you use two Workspace objects to open a table attached to an ODBC data source, transaction methods on one Workspace object will affect transactions on the other. Because it’s unusual to have two different sets of transactions used concurrently, this isn’t 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, which guarantees transaction isolation. The IsolateODBCTrans property can’t be used with an ODBCDirect Workspace object.