ComitTrans in a Form

[Herb]

I have an MSAccess form in "continuous forms" mode that is bound to a table.
Records can be added, deleted and modified within the form but there is a
command box that allows the user to CANCEL.

Is there a way to use BeginTrans, ComitTrans and Rollback to accomplish this?

From what I've read about this feature it has to be based on a connection
object and the form does not really open a connection object that I can see.

Any help would be greatly appreciated.

Thank you

 

[Dirk Goldgar]

I have an MSAccess form in "continuous forms" mode that is bound to a
table. Records can be added, deleted and modified within the form but
there is a command box that allows the user to CANCEL.

Is there a way to use BeginTrans, ComitTrans and Rollback to
accomplish this?

From what I've read about this feature it has to be based on a
connection object and the form does not really open a connection
object that I can see.

Any help would be greatly appreciated.

Thank you

It appears that you can do it, at least in Access 2002, if you open your
own ADO recordset and bind the form to that recordset, rather than using
the built-in form binding. I have only experimented with it briefly, so
there may well be "gotchas" involved in the process.

This is the code module from my simple test form:

'----- start of code -----
Option Compare Database
Option Explicit

Dim mConn As ADODB.Connection

Private Sub Form_Close()

With mConn
If MsgBox("Rollback?", vbYesNo) = vbYes Then
.RollbackTrans
Else
.CommitTrans
End If
End With

Set mConn = Nothing

End Sub

Private Sub Form_Open(Cancel As Integer)

Dim rst As New ADODB.Recordset
Dim strSQL As String

Set mConn = CurrentProject.Connection
mConn.BeginTrans

rst.CursorLocation = adUseClient
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
strSQL = "SELECT * FROM Table1"
rst.Open strSQL, mConn

Set Me.Recordset = rst

End Sub

'----- end of code -----

Please note that I don't vouch for this technique at all. I only tested
it briefly.

 

[Herb]

Thanks Dirk. I'll give it a try and let you know if I find any issues.

Herb