ACC: How to Enforce Transactions on Attached SQL Server Tables

 Last reviewed: May 20, 1997
Article 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
Hardware : X86
Issue type : kbhowto

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 20, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.