Using Start/Commit Transaction with Dao Recordset

  • Thread starter Thread starter Martureo Bob
  • Start date Start date
M

Martureo Bob

Hi;

I'm updating two Dao RecordSets in a VBA module. My sequence of events is
as follows:

"StartTrans"
Open both RecordSets
Make the necessary changes / additions to the data
Close the RecordSets
"CommitTrans"

Using the above, the records get updated properly.

BUT --- does it make a difference where I **close** the RecordSets? Am I
defeating the purpose of the Transaction if I close them both before the
"CommitTrans"? Or must I close them both after the "CommitTrans" in order
to have transaction handling on this?

Thanks!

Bob.
 
Martureo Bob said:
Hi;

I'm updating two Dao RecordSets in a VBA module. My sequence of
events is as follows:

"StartTrans"
Open both RecordSets
Make the necessary changes / additions to the data
Close the RecordSets
"CommitTrans"

Using the above, the records get updated properly.

BUT --- does it make a difference where I **close** the RecordSets?
Am I defeating the purpose of the Transaction if I close them both
before the "CommitTrans"? Or must I close them both after the
"CommitTrans" in order to have transaction handling on this?
Click to expand...

I would close them before committing the transaction. I don't know
offhand whether closing them *after* committing the transaction
interferes with the transaction in any way, but I'm sure it's not
required. And it makes sense to me that when I'm done updating, I close
the recordsets and then commit the transaction. That's how I've always
done it.
 
Hi Dirk,
In help all samples are done with first committing the transaction and the
closing recordset
so looks like there is no difference in this order
 
Dirk;

Have you successfully been able to rollback after the "close" ? That would
really put the nail in it, I suppose!

Bob.
 
Alex;

Thanks. I did notice that, but too late! My issue right now is that I have
a second-level subroutine that performs it's duties on one of the two
recordsets. That subroutine performs the open, the update and the close.
So when I return from that subroutine, the ResordSet is already closed. If
I had to do the commit before the close, I would have to make other
changes --- and that subroutine is called from about 15 other places within
the application. But there are only three places within the entire
application where I need to perform the StartTrans and CommitTrans.

So I'm really hoping I can do the commit after the close!

Bob.
 
Martureo Bob said:
Dirk;

Have you successfully been able to rollback after the "close" ? That
would really put the nail in it, I suppose!
Click to expand...

Yes. Test it yourself:

'----- start of example code -----
Sub TestTransaction()

Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set ws = Application.DBEngine.Workspaces(0)
Set db = ws.Databases(0)

ws.BeginTrans

Set rs = db.OpenRecordset("Table1")

' Note: Table1 contains these fields:
' ID (autonumber)
' Description (text)
' Modified (date/time)

rs.AddNew
rs!Description = "Added inside transaction"
rs!Modified = Now()
rs.Update

rs.Close
Set rs = Nothing
Set db = Nothing

If MsgBox("Commit?", vbYesNo) = vbYes Then
ws.CommitTrans
Else
ws.Rollback
End If

End Sub

'----- end of example code -----
 
I just tested it, and you're correct!

That sure makes things simpler for me!

THANKS!

Bob.
 
Back
Top