transaction implementation

[-Obama]

I have created an access code to insert 7 tables to MS 2005 server Database
with Access 97. I do like following:

table1.addnew
table2.addnew
....
table7.addnew
....
(insert records to table1, table2, ...)
....


table1. update
table2. update
....
table7.update



Now if I want to implementation, how can I do it? Say if table3.update is
failed after table1.update and table2.update ar done, how do I rollback so
that table1 and table2 are undo the insert records. Please adivse. Thanks.

 

[Pat Hartman]

You need to enclose the whole update procedure in a transaction. Lookup
Begin Tran, End Tran, Commit, and Rollback for details. I don't even
remember if A97 supports transactions but I assume it does.

 

[Ken Sheridan]

Here's a simple little procedure I use to demonstrate transaction handling
with DAO in Access. Each of the two 'update' queries qryU1 and qryU2 can be
made to fail by amending them so that they violate an index. If either fail
then the error handler rolls back the transaction.

Public Sub TransactionDemo()

Dim dbs As DAO.Database
Dim wrk As DAO.Workspace
Dim strQuery As String, strMessage As String

On Error GoTo Err_Handler

Set wrk = DAO.DBEngine.Workspaces(0)
Set dbs = CurrentDb

wrk.BeginTrans
strQuery = "qryU1"
dbs.Execute strQuery, dbFailOnError
strQuery = "qryU2"
dbs.Execute strQuery, dbFailOnError
wrk.CommitTrans

Exit_Here:
Exit Sub

Err_Handler:
If Err.Number = 3022 And dbs.QueryDefs(strQuery).Type = dbQAppend Then
dbs.Execute strQuery
Resume Next
Else
strMessage = Error & " (" & Err.Number & ")" & vbNewLine & vbNewLine
& _
"(Error in " & strQuery & ")" & _
vbNewLine & vbNewLine & "Transaction rolled back and no tables
updated."
MsgBox strMessage, vbExclamation, "Error"

wrk.Rollback
End If
Resume Exit_Here

End Sub

Ken Sheridan
Stafford, England