Using Start/Commit Transaction with Dao Recordset

[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.

 

[Dirk Goldgar]

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?

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.

 

[Alex Dybenko]

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

 

[Martureo Bob]

Dirk;

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

Bob.

 

[Martureo 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.

 

[Dirk Goldgar]

Dirk;

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

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 -----

 

[Martureo Bob]

I just tested it, and you're correct!

That sure makes things simpler for me!

THANKS!

Bob.