Transaction Boundaries... "Martureo Bob" <bob[ at ]martureo.org> 30.01.2006 16:51:44 Hi; In a module, I'm updating two Dao RecordSets. So I issue a "StartTrans" at the beginning, then open the RecordSets, make the necessary changes to the the RecordSets, close the RecordSets, and finally issue a "CommitTrans". The records get updated properly. Question: 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"? Thanks! Bob.

Re: Transaction Boundaries... "Douglas J Steele" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> 31.01.2006 11:53:30 I believe you should issue the CommitTrans before you close the recordset(s). Certainly, if you need to issue a Rollback, I would think it would have to be done before the recordset is closed. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Martureo Bob" <bob[ at ]martureo.org> wrote in message news:Oz1kV2bJGHA.3100[ at ]tk2msftngp13.phx.gbl... [Quoted Text] > Hi; > > In a module, I'm updating two Dao RecordSets. So I issue a "StartTrans" at > the beginning, then open the RecordSets, make the necessary changes to the > the RecordSets, close the RecordSets, and finally issue a "CommitTrans". > The records get updated properly. > > Question: 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"? > > Thanks! > > Bob. > >

Re: Transaction Boundaries... "Dirk Goldgar" <dg[ at ]NOdataSPAMgnostics.com> 31.01.2006 14:39:16 "Douglas J Steele" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> wrote in message news:%230ql1zlJGHA.916[ at ]TK2MSFTNGP10.phx.gbl [Quoted Text] > I believe you should issue the CommitTrans before you close the > recordset(s). Certainly, if you need to issue a Rollback, I would > think it would have to be done before the recordset is closed. I've tested it, and that does not appear to be the case. '----- 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 ----- -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup)

Re: Transaction Boundaries... "Douglas J Steele" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> 31.01.2006 14:43:14 "Dirk Goldgar" <dg[ at ]NOdataSPAMgnostics.com> wrote in message news:O2L4yPnJGHA.3064[ at ]TK2MSFTNGP10.phx.gbl... [Quoted Text] > "Douglas J Steele" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> wrote in message > news:%230ql1zlJGHA.916[ at ]TK2MSFTNGP10.phx.gbl > > I believe you should issue the CommitTrans before you close the > > recordset(s). Certainly, if you need to issue a Rollback, I would > > think it would have to be done before the recordset is closed. > > I've tested it, and that does not appear to be the case. > > '----- 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 ----- That's what I get for being too lazy to test. <g> Thanks for the correct information, Dirk. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!)