| Microsoft Office 2000/Visual Basic Programmer's Guide | |
The Jet database engine only supports transactions against Access databases (including databases from previous versions of Access) and ODBC data sources. For example, if your database has linked Excel or dBASE tables, any transactions on those objects are ignored. This means that the transaction will not fail or generate a run-time error, but it won't actually do anything, either. Note that even though Microsoft Jet 2.x and 3.x databases (Access 2.0, 95, and 97 databases) are opened by Microsoft Jet 4.0 as external ISAM databases, the engine does support transactions on those databases.
However, you may be able to use ADO code to perform transactions against other data sources by using a different OLE DB provider. For example, if you need to write code to work directly with a SQL Server database, you can use ADO code with the Microsoft OLE DB Provider for SQL Server (Provider=SQLOLEDB) to perform transactions. For more information about available OLE DB providers, see http://www.microsoft.com/data/oledb/partners/products.htm.
Regardless of the OLE DB provider you are using, you can check the value of the provider-specific Transaction DDL property in the Properties collection of the Connection object to determine whether the object supports transactions. A value of 0 indicates that the object doesn’t support transactions. The value for Transaction DDL on a Connection object that uses the Microsoft Jet 4.0 OLE DB Provider to connect to an Access database is 16, which indicates that both SQL Data Definition Language (DDL) and Data Manipulation Language (DML) statements can be used within a transaction, but DDL statements that modify tables or indexes will lock those objects until the transaction finishes. This means you won’t be able to execute multiple statements if a subsequent SQL statement needs to place a lock on an object that has already been locked by a previous statement. The table below describes all possible values of the Transaction DDL property.
| Transaction DDL value | Description |
| 0 | Transactions are not supported. |
| 1 | Transactions can only contain DML statements. DDL statements within a transaction cause an error. |
| 2 | Transactions can only contain DML statements. DDL statements within a transaction cause the transaction to be committed. |
| 4 | Transactions can only contain DML statements. DDL statements within a transaction are ignored. |
| 8 | Transactions can contain DDL and DML statements in any order. |
| 16 | Transactions can contain both DML and DDL statements that modify tables or indexes, but modifying a table or index within a transaction causes the table or index to be locked until the transaction completes. This means you won't be able to execute additional statements that affect the tables or indexes once they are locked. |