Microsoft KB Archive/200880

{|
 * width="100%"|

ACC2000: Controlling Bound Form Transactions in Access Projects

 * }

Q200880

-

The information in this article applies to:


 * Microsoft Access 2000

-

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

This article applies only to a Microsoft Access project (.adp).

SUMMARY
This article describes how you can control transactions in a form based on a SQL Server in a Microsoft Access project (.adp) file.

MORE INFORMATION
Typically, a developer needs to be able to let users of a database application commit all or none of their data changes on a form. Microsoft Access 97 and earlier did not provide this capability because transactions in bound forms were handled in a separate workspace controlled by Access.

However, in Access 2000, a developer can use a combination of ActiveX Data Objects (ADO) and the new form Recordset property to provide this capability. The example in this article assumes that the user wants to be prompted to commit all changes made to the form's data when the user closes the form. At that time, the user can either commit all of the changes or none of the changes.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please see the following page on the World Wide Web:

"http://www.microsoft.com/partner/referral/" For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

"http://support.microsoft.com/directory/overview.asp" NOTE: The sample code in this article uses Microsoft ActiveX Data Objects. For this code to run properly, you must reference the Microsoft ActiveX Data Objects 2.1 Library. To do so, click References on the Tools menu in the Visual Basic Editor, and ensure that the Microsoft ActiveX Data Objects 2.1 Library check box is selected.

NOTE: This example is designed to work with single main forms. It is not designed to work with linked subforms.

Controlling Transactions on Bound Forms

 * 1) Create a new Microsoft Access project (ADP) based on the Pubs database from SQL Server.
 * 2) Create a new form based on the Authors table, and open it in Design view.
 * 3) Add all fields to the form's detail section.
 * 4) On the View menu, click Code to view the form's module.
 * 5) Add the following code to the form's module:
 * 6) On the Debug menu, click Compile .
 * 7) On the File menu, click Save . When prompted, save the form as frmAuthors.
 * 8) Open the form in Form view. Note that the form populates with data.
 * 9) Edit an existing record, insert some new records, and delete some records.
 * 10) Close the form. Note that you are prompted if you want to commit all changes.
 * 11) Click No.
 * 12) Open the form in Form view. Note that none of the changes that you made in step 9 was saved to the database.
 * 13) Repeat steps 8 and 9.
 * 14) When prompted to commit all changes, click Yes.
 * 15) Open the form in Form view. Note that all the changes that you made were saved to the database.

Additional query words: inf ado commit rollback

Keywords : kbdta AccessCS

Issue type : kbhowto

Technology :