ACC: How to Enforce Transactions on Attached SQL Server Tables (95607)


The information in this article applies to:

  • Microsoft Access 1.0
  • Microsoft Access 1.1
  • Microsoft Access 2.0
This article was previously published under Q95607

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.
Modification Type:MajorLast Reviewed:5/9/2003
Keywords:kbhowto kbusage KB95607
©2002 Microsoft Corporation. All rights reserved.