Microsoft KB Archive/95607

= ACC: How to Enforce Transactions on Attached SQL Server Tables =

Article ID: 95607

Article Last Modified on 5/9/2003

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition

-



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 &quot;CreateDynaset&quot; 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(&quot;Table1&quot;)  MyDyna.Close CommitTrans/Rollback

***CORRECT*** Dim MyDyna As Dynaset MyDyna = CreateDynaset(&quot;Table1&quot;) BeginTrans  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: kbhowto kbusage KB95607

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.