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