Q200880

• Microsoft Access 2000

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/

http://support.microsoft.com/directory/overview.asp

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:




Option Compare Database
Option Explicit

Private boolFrmDirty As Boolean
Private boolFrmSaved As Boolean
Private adoConnection As New ADODB.Connection
Private adoRecordset As New ADODB.Recordset

Private Sub Form_AfterDelConfirm(Status As Integer)
   If Me.Saved = False Then Me.Saved = (Status = acDeleteOK)
End Sub

Private Sub Form_AfterUpdate()
   Me.Saved = True
End Sub

Private Sub Form_Delete(Cancel As Integer)
   If Me.Dirtied = False Then adoConnection.BeginTrans
   Me.Dirtied = True
End Sub

Private Sub Form_Dirty(Cancel As Integer)
   If Me.Dirtied = False Then adoConnection.BeginTrans
   Me.Dirtied = True
End Sub

Private Sub Form_Open(Cancel As Integer)
   Dim strConnect As String

   ' You will need to modify the following string so the 
   ' Server Name, User ID, and Password are correct.
   strConnect = "DATA PROVIDER=SQLOLEDB;DATA SOURCE=Myserver;" & _
      "UID=sa;PWD=;DATABASE=Pubs"

   adoConnection.Provider = "MSDataShape"
   adoConnection.Open strConnect
   adoRecordset.Open "SELECT * FROM Authors", adoConnection, _
      adOpenKeyset, adLockOptimistic
   Set Me.Recordset = adoRecordset
   Me.UniqueTable = "Authors"
End Sub

Private Sub Form_Unload(Cancel As Integer)
   Dim msg As Integer
   If Me.Saved Then
      msg = MsgBox("Do you want to commit all changes?", _
         vbYesNoCancel)
      Select Case msg
         Case vbYes
            adoConnection.CommitTrans
         Case vbNo
            adoConnection.RollbackTrans
         Case vbCancel
            Cancel = True
      End Select
   Else
      If Me.Dirtied Then adoConnection.RollbackTrans
   End If
End Sub

Public Property Get Dirtied() As Boolean
   Dirtied = boolFrmDirty
End Property

Public Property Let Dirtied(ByVal boolNewValue As Boolean)
   boolFrmDirty = boolNewValue
End Property

Public Property Get Saved() As Boolean
   Saved = boolFrmSaved
End Property

Public Property Let Saved(ByVal boolNewValue As Boolean)
   boolFrmSaved = boolNewValue
End Property 

6. On the Debug menu, click Compile <project name>.



7. On the File menu, click Save <project name>. 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 :