ACC: How to Enforce Transactions on Attached SQL Server Tables

ID: Q95607

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

Microsoft Access will automatically support transaction processing on attached SQL Server tables using queries. However, explicit transactions in Access Basic require the use of dynasets on attached tables and further require a specific order in the creation of the dynasets and the implementation of transaction processing.

MORE INFORMATION

Microsoft Access will enforce transaction processing on an attached SQL Server table through the use of a dynaset created on that table. For more information about how to create a dynaset on an attached SQL Server table, search for "CreateDynaset" using the Help menu. The key to making transaction processing work for attached SQL Server tables is to create and close the dynaset on the attached SQL Server table outside the transaction. Below are pseudo-code examples of the incorrect and correct methods of coding this process:


   ***INCORRECT***
   Dim MyDyna As Dynaset
   BeginTrans
      MyDyna = CreateDynaset("Table1")
      <misc.code such as Inserts/Updates/Deletes.>
      MyDyna.Close
   CommitTrans/Rollback

   ***CORRECT***
   Dim MyDyna As Dynaset
   MyDyna = CreateDynaset("Table1")
   BeginTrans
      <misc.code such as Inserts/Updates/Deletes.>
   CommitTrans/Rollback
   MyDyna.close

NOTE: Improvements to the Microsoft Jet database engine version 3.0 remote transaction management now allow seamless use of server transactions in Visual Basic for Applications. The methods listed above work in the Jet Database engine version 3.0 and above.

Keywords : kbusage OdbcOthr
Version : 1.0 1.1 2.0
Platform : WINDOWS
Issue type : kbhowto