Way to test if transactions are working?

[TD]

Below is the code I have under a "Commit" button on a bound form. It
works fine but this is the first time I have used transactions and I
would like someone to review it and let me know if there is any thing I
am doing wrong. Also, is there a way to test this to see what happens
during a rollback?

Thanks,
Thomas

Private Sub cmdCommit_Click()
On Error GoTo Err_cmdCommit_Click

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String
Dim AppPath As String
sql = ""
sql = sql & "SELECT tblInvMovement.DateMoved,
tblInvMovement.LotNumber, tblInvMovement.VPartNumber, "
sql = sql & "tblInvMovement.PPartNumber,
tblInvMovement.Description, tblInvMovement.Qnty, "
sql = sql & "tblInvMovement.From, tblInvMovement.To FROM
tblInvMovement;"

AppPath = CurrentDb.Name
Set db = OpenDatabase(AppPath)

Set rst = db.OpenRecordset(sql, dbOpenSnapshot)
'--------------------------------------------------------------------
DBEngine.BeginTrans

Do Until rst.EOF
db.Execute "INSERT INTO tblInventory ( Event_Date,
LotNumber, VPartNumber, PPartNumber, Description, " & _
"" & rst(6) & ", " & rst(7) & ") VALUES ('" &
rst(0) & "', '" & rst(1) & "', '" & rst(2) & "', " & _
"'" & rst(3) & "', '" & rst(4) & "', '" &
(-rst(5)) & "', '" & rst(5) & "');", dbFailOnError
rst.MoveNext
Loop

db.Execute "DELETE * FROM tblInvMovement;", dbFailOnError

DBEngine.CommitTrans dbFlushOSCacheWrites

'--------------------------------------------------------------------

rst.Close
db.Close

Forms![frmInvMovement]![frmSubInvMovement].Requery
Exit_cmdCommit_Click:
Exit Sub

Err_cmdCommit_Click:
DBEngine.Rollback
MsgBox Err.Description
Resume Exit_cmdCommit_Click

End Sub

[Tom van Stiphout]

On 6 Jun 2006 06:40:36 -0700, "TD" <dl**@adsi-sc.com> wrote:

Your code seems OK.
To test a rollback, cause an error in the loop. See Err.Raise.

The more fundamental point: why use this loop, rather than a single
insert statement:
insert into tblInventory (...)
select ... from tblInvMovement

-Tom.

Below is the code I have under a "Commit" button on a bound form. It
works fine but this is the first time I have used transactions and I
would like someone to review it and let me know if there is any thing I
am doing wrong. Also, is there a way to test this to see what happens
during a rollback?

Thanks,
Thomas

Private Sub cmdCommit_Click()
On Error GoTo Err_cmdCommit_Click

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String
Dim AppPath As String
sql = ""
sql = sql & "SELECT tblInvMovement.DateMoved,
tblInvMovement.LotNumber, tblInvMovement.VPartNumber, "
sql = sql & "tblInvMovement.PPartNumber,
tblInvMovement.Description, tblInvMovement.Qnty, "
sql = sql & "tblInvMovement.From, tblInvMovement.To FROM
tblInvMovement;"

AppPath = CurrentDb.Name
Set db = OpenDatabase(AppPath)

Set rst = db.OpenRecordset(sql, dbOpenSnapshot)
'--------------------------------------------------------------------
DBEngine.BeginTrans

Do Until rst.EOF
db.Execute "INSERT INTO tblInventory ( Event_Date,
LotNumber, VPartNumber, PPartNumber, Description, " & _
"" & rst(6) & ", " & rst(7) & ") VALUES ('" &
rst(0) & "', '" & rst(1) & "', '" & rst(2) & "', " & _
"'" & rst(3) & "', '" & rst(4) & "', '" &
(-rst(5)) & "', '" & rst(5) & "');", dbFailOnError
rst.MoveNext
Loop

db.Execute "DELETE * FROM tblInvMovement;", dbFailOnError

DBEngine.CommitTrans dbFlushOSCacheWrites

'--------------------------------------------------------------------

rst.Close
db.Close

Forms![frmInvMovement]![frmSubInvMovement].Requery
Exit_cmdCommit_Click:
Exit Sub

Err_cmdCommit_Click:
DBEngine.Rollback
MsgBox Err.Description
Resume Exit_cmdCommit_Click

End Sub

[Rick Wannall]

(I originally posted this answer in reponse to "References to CurrenbDb",
but it seems relevant to what you're doing.)

Been a long time since I've done transactions in Access, but the example in
help these days (which matches my memory of how I was doing it), shows use
of BeginTrans et al off the Workspace object.

The general approach is this:

Public Function testws()

Dim ws As Workspace
Set ws = DBEngine.Workspaces(0)

On Error GoTo Generic_Error
'prep work here

ws.BeginTrans
On Error GoTo RollMeBack

'insert
'update
'delete
'whatever

'finished, commit
ws.CommitTrans
Exit_testws:
Set ws = Nothing

Generic_Error:
MsgBox err.Description
Resume Exit_testws

RollMeBack:
MsgBox err.Description, vbCritical, "Rolling Back After Error"
ws.Rollback
Resume Exit_testws

End Function
You can get much more detailed and even nest transactions several layers
deep, though I can't remember why on earth I thought I needed to do that any
more.

Does this help?

Someone more current on Transactions and Access could explain whether it is
better in any way to perform transactions off of a database object or a
workspace object. The Help says that BeginTrans et al apply to database or
workspace, but I've only ever used them from a workspace object, I believe.

[Rick Wannall]

Add "Exit Function" after "Set ws = nothing

[TD]

How does this look? Just want to be sure I'm doing it correctly!

Thanks,
TD

Private Sub cmdCommit_Click()
On Error GoTo Err_cmdCommit_Click

Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String
Dim AppPath As String
Dim Response As String

Response = MsgBox("The movements will be added to the inventory" &
vbCrLf & " and the handheld device will be cleared! " & vbCrLf &
" Continue? ", vbYesNo +
vbDefaultButton2, " Are you sure? ")

If Response = vbNo Then
GoTo Exit_cmdCommit_Click:
End If

If Response = vbYes Then

Response = MsgBox("Have you printed the movements?" & vbCrLf &
" Continue? ", vbYesNo +
vbDefaultButton2, "WAIT!")

If Response = vbNo Then
GoTo Exit_cmdCommit_Click:
End If

sql = ""
sql = sql & "SELECT tblInvMovement.DateMoved,
tblInvMovement.LotNumber, tblInvMovement.VendorPartNumber, "
sql = sql & "tblInvMovement.ProtecPartNumber,
tblInvMovement.Description, tblInvMovement.Qnty, "
sql = sql & "tblInvMovement.From, tblInvMovement.To FROM
tblInvMovement;"

Set ws = DBEngine.Workspaces(0)

AppPath = CurrentDb.Name
Set db = OpenDatabase(AppPath)

Set rst = db.OpenRecordset(sql, dbOpenSnapshot)
'--------------------------------------------------------------------
ws.BeginTrans
On Error GoTo Err_Rollback

Do Until rst.EOF
db.Execute "INSERT INTO tblInventory ( Event_Date,
LotNumber, VendorPartNumber, ProtecPartNumber, Description, " & _
"" & rst(6) & ", " & rst(7) & ") VALUES ('" &
rst(0) & "', '" & rst(1) & "', '" & rst(2) & "', " & _
"'" & rst(3) & "', '" & rst(4) & "', '" &
(-rst(5)) & "', '" & rst(5) & "');", dbFailOnError
rst.MoveNext
Loop

db.Execute "DELETE * FROM tblInvMovement;", dbFailOnError

ws.CommitTrans dbFlushOSCacheWrites

'--------------------------------------------------------------------

End If

Forms![frmInvMovement]![frmSubInvMovement].Requery

Exit_cmdCommit_Click:
rst.Close
db.Close
ws.Close
Exit Sub

Err_Rollback:
MsgBox Err.Description, vbCritical, "An error occurred, rolling
back changes"
Resume Exit_cmdCommit_Click

Err_cmdCommit_Click:
MsgBox Err.Description
Resume Exit_cmdCommit_Click

End Sub

[Rick Wannall]

On 7-Jun-2006, "TD" <dl**@adsi-sc.com> wrote:

Err_Rollback:
MsgBox Err.Description, vbCritical, "An error occurred, rolling
back changes"
Resume Exit_cmdCommit_Click
Looks about right. One or two trimups:

Make sure you get a ws.Rollback in there, presumably just below the MsgBox
in the code I quoted above.

Also, watch placement of .Close statements. In this code:

Exit_cmdCommit_Click:
rst.Close
db.Close
ws.Close
Exit Sub

I would use
Exit_cmdCommit_Click:
Set rst = nothing
Set db = nothing
Set ws = nothing
Exit Sub
Then, put your .Close statements inline just after the last line of code
that uses the object. It's more important to destroy than to close, so you
destroy regardless of the path you take, but it's sufficient to close on the
normal execution path.

I stand to be corrected, but I believe that you will have no problems from
not closing, while you can definitely have problems from not destroying.

[TD]

THANKS!!!

Rick Wannall wrote:

On 7-Jun-2006, "TD" <dl**@adsi-sc.com> wrote:

Err_Rollback:
MsgBox Err.Description, vbCritical, "An error occurred, rolling
back changes"
Resume Exit_cmdCommit_Click

Looks about right. One or two trimups:

Make sure you get a ws.Rollback in there, presumably just below the MsgBox
in the code I quoted above.

Also, watch placement of .Close statements. In this code:

Exit_cmdCommit_Click:
rst.Close
db.Close
ws.Close
Exit Sub

I would use
Exit_cmdCommit_Click:
Set rst = nothing
Set db = nothing
Set ws = nothing
Exit Sub
Then, put your .Close statements inline just after the last line of code
that uses the object. It's more important to destroy than to close, so you
destroy regardless of the path you take, but it's sufficient to close on the
normal execution path.

I stand to be corrected, but I believe that you will have no problems from
not closing, while you can definitely have problems from not destroying.

[cjb_kjb]

The best way I know to test Rollbacks, rather than conjuring up error
conditions, is to set a break point in your code at the commit and when
it reaches that point change the commit to a rollback (dont forget to
change it back) and let the code proceed. Then go to the tables and
check they have the same records as before you started.

TD wrote:

THANKS!!!

Rick Wannall wrote:

On 7-Jun-2006, "TD" <dl**@adsi-sc.com> wrote:

Err_Rollback:
MsgBox Err.Description, vbCritical, "An error occurred, rolling
back changes"
Resume Exit_cmdCommit_Click

Looks about right. One or two trimups:

Make sure you get a ws.Rollback in there, presumably just below the MsgBox
in the code I quoted above.

Also, watch placement of .Close statements. In this code:

Exit_cmdCommit_Click:
rst.Close
db.Close
ws.Close
Exit Sub

I would use
Exit_cmdCommit_Click:
Set rst = nothing
Set db = nothing
Set ws = nothing
Exit Sub
Then, put your .Close statements inline just after the last line of code
that uses the object. It's more important to destroy than to close, so you
destroy regardless of the path you take, but it's sufficient to close on the
normal execution path.

I stand to be corrected, but I believe that you will have no problems from
not closing, while you can definitely have problems from not destroying.