Rolling back transactions

B

Brad Pears

I am implementing some "roll back" code into an existing application. When
testing, I have discovered that the roll back was not working with the
intial code I was using in the sub procedure - where the SQL insert was
actually performed... In order for it to work, I had to pass the "db" object
as one of the parameters into the sub procedure so I could issue a
"db.exectute strSQL" statement instead of the "docmd.execute strSQL" I had
been using. Is there a problem with passing DB as a parameter or is there a
better way to code this at all? Also, should the "docmd" code have been
"rollbackable"??

I have a main procedure where I start the transaction. This procedure calls
a subprocedure and it is here that an SQL insert statement is issued. Upon
return to the main procedure I am issuing a rollback right away (testing
only) .

Here is some sample code where I insert a row and then immediately roll it
back for test purposes only.

Sub MainProc1
Dim ws As DAO.Workspace ' Default workspace
Dim db As DAO.Database ' Default database
Dim bolTransAct As Boolean ' Flag that transaction is active

'Begin a transaction
Set ws = DBEngine(0)
ws.BeginTrans
bolTransAct = True

' Initialize the db object within the transaction
Set db = ws(0)

' Insert a note into the notes table
Call InsertBatchNote(CustID, Note, now(), currentuser(), db)

Set db = Nothing
' Roll back all changes
If bolTransAct Then
ws.Rollback
End If
Set ws = Nothing

end sub


' Here is the sub proc wher ethe note is inserted
Sub InsertBatchNote(intCustID As Long, NoteMess As String, NoteDate As
String, EmpID As String, db as dao.database)
' This function inserts a note into the NOTES table
Dim SQLstmt As String
Dim StrNoteMess As String
Dim strNoteDate As String
Dim strEmpId As String

' Handle errors...
On Error GoTo SQLError

' Set confirmation off for action queries
DoCmd.SetWarnings False

' Set up the string values to insert into the Notes table
StrNoteMess = "'" & NoteMess & "'"
strNoteDate = "'" & NoteDate & "'"
strEmpId = "'" & EmpID & "'"

' Setup the insert SQl statement
SQLstmt = "insert into NOTES ([CustomerID], [Note], [NoteDate], [EmpInit])
values (" & intCustID & ", " & StrNoteMess & "," & strNoteDate & "," &
strEmpId & ");"

' Insert a Note stating an Update letter was sent..

' This was the code I was using - the rollback did not work when using this
'DoCmd.RunSQL (SQLstmt)

' So I changed it to this... and now it works (after passing in the db
object to this proc)
db.execute SQLstmt, dbfailonerror

' Turn warnings back on
DoCmd.SetWarnings True

Exit Sub

SQLError:
' Set confirmation messages back on

' Display Error message
MsgBox "An error has occurred while inserting batch note..." & vbCrLf & _
"CustID: " & intCustID & vbCrLf & _
"Inform I.T. of the problem."

End Sub


------------------------------------------------------------------------------------------------------------------------------------------
So my question is - is this the right way to do this? Should I be specifying
"By REF" when passing the db object (assumed correct??)


Thanks, Brad
 
A

Allen Browne

Your obvervations are spot on. If you OpenQuery or RunSQL on an action
query, the action does not occur inside the transaction, and so does not
rollback.

So, passing the database variable so you can Execute your query is the
perfect way to approach this.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Brad Pears said:
I am implementing some "roll back" code into an existing application. When
testing, I have discovered that the roll back was not working with the
intial code I was using in the sub procedure - where the SQL insert was
actually performed... In order for it to work, I had to pass the "db"
object as one of the parameters into the sub procedure so I could issue a
"db.exectute strSQL" statement instead of the "docmd.execute strSQL" I had
been using. Is there a problem with passing DB as a parameter or is there a
better way to code this at all? Also, should the "docmd" code have been
"rollbackable"??

I have a main procedure where I start the transaction. This procedure
calls a subprocedure and it is here that an SQL insert statement is
issued. Upon return to the main procedure I am issuing a rollback right
away (testing only) .

Here is some sample code where I insert a row and then immediately roll it
back for test purposes only.

Sub MainProc1
Dim ws As DAO.Workspace ' Default workspace
Dim db As DAO.Database ' Default database
Dim bolTransAct As Boolean ' Flag that transaction is active

'Begin a transaction
Set ws = DBEngine(0)
ws.BeginTrans
bolTransAct = True

' Initialize the db object within the transaction
Set db = ws(0)

' Insert a note into the notes table
Call InsertBatchNote(CustID, Note, now(), currentuser(), db)

Set db = Nothing
' Roll back all changes
If bolTransAct Then
ws.Rollback
End If
Set ws = Nothing

end sub


' Here is the sub proc wher ethe note is inserted
Sub InsertBatchNote(intCustID As Long, NoteMess As String, NoteDate As
String, EmpID As String, db as dao.database)
' This function inserts a note into the NOTES table
Dim SQLstmt As String
Dim StrNoteMess As String
Dim strNoteDate As String
Dim strEmpId As String

' Handle errors...
On Error GoTo SQLError

' Set confirmation off for action queries
DoCmd.SetWarnings False

' Set up the string values to insert into the Notes table
StrNoteMess = "'" & NoteMess & "'"
strNoteDate = "'" & NoteDate & "'"
strEmpId = "'" & EmpID & "'"

' Setup the insert SQl statement
SQLstmt = "insert into NOTES ([CustomerID], [Note], [NoteDate], [EmpInit])
values (" & intCustID & ", " & StrNoteMess & "," & strNoteDate & "," &
strEmpId & ");"

' Insert a Note stating an Update letter was sent..

' This was the code I was using - the rollback did not work when using
this
'DoCmd.RunSQL (SQLstmt)

' So I changed it to this... and now it works (after passing in the db
object to this proc)
db.execute SQLstmt, dbfailonerror

' Turn warnings back on
DoCmd.SetWarnings True

Exit Sub

SQLError:
' Set confirmation messages back on

' Display Error message
MsgBox "An error has occurred while inserting batch note..." & vbCrLf &
_
"CustID: " & intCustID & vbCrLf & _
"Inform I.T. of the problem."

End Sub


------------------------------------------------------------------------------------------------------------------------------------------
So my question is - is this the right way to do this? Should I be
specifying "By REF" when passing the db object (assumed correct??)


Thanks, Brad
 
B

Brad Pears

Great, thanks for the re-enforcement! Much appreciated!

Brad
Allen Browne said:
Your obvervations are spot on. If you OpenQuery or RunSQL on an action
query, the action does not occur inside the transaction, and so does not
rollback.

So, passing the database variable so you can Execute your query is the
perfect way to approach this.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Brad Pears said:
I am implementing some "roll back" code into an existing application. When
testing, I have discovered that the roll back was not working with the
intial code I was using in the sub procedure - where the SQL insert was
actually performed... In order for it to work, I had to pass the "db"
object as one of the parameters into the sub procedure so I could issue a
"db.exectute strSQL" statement instead of the "docmd.execute strSQL" I had
been using. Is there a problem with passing DB as a parameter or is there
a better way to code this at all? Also, should the "docmd" code have been
"rollbackable"??

I have a main procedure where I start the transaction. This procedure
calls a subprocedure and it is here that an SQL insert statement is
issued. Upon return to the main procedure I am issuing a rollback right
away (testing only) .

Here is some sample code where I insert a row and then immediately roll
it back for test purposes only.

Sub MainProc1
Dim ws As DAO.Workspace ' Default workspace
Dim db As DAO.Database ' Default database
Dim bolTransAct As Boolean ' Flag that transaction is active

'Begin a transaction
Set ws = DBEngine(0)
ws.BeginTrans
bolTransAct = True

' Initialize the db object within the transaction
Set db = ws(0)

' Insert a note into the notes table
Call InsertBatchNote(CustID, Note, now(), currentuser(), db)

Set db = Nothing
' Roll back all changes
If bolTransAct Then
ws.Rollback
End If
Set ws = Nothing

end sub


' Here is the sub proc wher ethe note is inserted
Sub InsertBatchNote(intCustID As Long, NoteMess As String, NoteDate As
String, EmpID As String, db as dao.database)
' This function inserts a note into the NOTES table
Dim SQLstmt As String
Dim StrNoteMess As String
Dim strNoteDate As String
Dim strEmpId As String

' Handle errors...
On Error GoTo SQLError

' Set confirmation off for action queries
DoCmd.SetWarnings False

' Set up the string values to insert into the Notes table
StrNoteMess = "'" & NoteMess & "'"
strNoteDate = "'" & NoteDate & "'"
strEmpId = "'" & EmpID & "'"

' Setup the insert SQl statement
SQLstmt = "insert into NOTES ([CustomerID], [Note], [NoteDate],
[EmpInit]) values (" & intCustID & ", " & StrNoteMess & "," & strNoteDate
& "," & strEmpId & ");"

' Insert a Note stating an Update letter was sent..

' This was the code I was using - the rollback did not work when using
this
'DoCmd.RunSQL (SQLstmt)

' So I changed it to this... and now it works (after passing in the db
object to this proc)
db.execute SQLstmt, dbfailonerror

' Turn warnings back on
DoCmd.SetWarnings True

Exit Sub

SQLError:
' Set confirmation messages back on

' Display Error message
MsgBox "An error has occurred while inserting batch note..." & vbCrLf &
_
"CustID: " & intCustID & vbCrLf & _
"Inform I.T. of the problem."

End Sub


------------------------------------------------------------------------------------------------------------------------------------------
So my question is - is this the right way to do this? Should I be
specifying "By REF" when passing the db object (assumed correct??)


Thanks, Brad
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top