BeginTrans, CommitTrans, & RollbackTrans

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to the statements BeginTrans, CommitTrans and RollbackTrans to my code with no luck.

I have tried
Dim DAODB as DAO.Database
Dim rst as DAO.Recordset
Dim SQL as string

Set DAODB = CurrentDB
DAODB.BeginTrans

Lots of code update a table several times as follows

SQL = = "SELECT......."
Set rst = mDAODB.OpenRecordSet(SQL, dbOpenDynaset)

rst.Edit
kjdhffjashpuioqsygiuj
rst.Update

rst.close


SQL = = "SELECT......."
Set rst = mDAODB.OpenRecordSet(SQL, dbOpenDynaset)

rst.Edit
kjdhffjashpuioqsygiuj
rst.Update

rst.close

etc

DAODB.Close

I can't seem to get it work and no clue how.

Any help would be appreciated...

Thanks
 
Hi Sheldon,
You are using DAO and therefore need to use a DAO Workspace. Have a look at
the following and and change it to suit your needs. Remember to amend the
error trapping for your requirements.
Regards
Terry


Dim ws As DAO.Workspace, db As DAO.Database, rs As DAO.Recordset
Dim booSaved As Boolean

' set save flag
booSaved = True

Set ws = DBEngine(0)
Set db = CurrentDb
Set rs = db.OpenRecordset("NCRPreAlert", dbOpenDynaset)
booSaved = False
ws.BeginTrans
rs.Edit
rs!NCRNumber = lngIDNCR
rs!OtherFields= etc. etc

rs.Update
ws.CommitTrans
booSaved = True

Exit_Event:
If booSaved = False Then ' Save failed, rollback
booSaved = True
ws.Rollback
End If
rs.Close
Set rs = Nothing
Set db = Nothing
Set ws = Nothing
booSaved = True
Exit Sub

Err_Event:
MsgBox "oops! I encountered an error, your change was not saved." &
vbCrLf & vbCrLf & _
"Please try again. If the error persists please advise this System
Administrator." & vbCrLf & _
"Error: " & Err.Number & " " & Err.Description, vbOKOnly + vbCritical,
"Error Encountered"
Resume Exit_Event
 
Back
Top