Using Transactions

J

Jim Franklin

Hi,

I have tried using a transaction to make sure that changes to a number of
tables are completed successfully, or all rolled back. However, I cannot get
the transaction to rollback, and the changes are applied regardless.

My test code below deletes all the records from a series of tables. Even
when I select No at the end, all my tables end up empty!

Any help is very much appreciated!

Cheers,
Jim

'set workspace for transaction
Set wrkDefault = DBEngine.Workspaces(0)

MyArray(0) = "tbl_Users"
MyArray(1) = "tbl_Applicants"
MyArray(2) = "tbl_Cases"
MyArray(3) = "tbl_CaseApplicants"
MyArray(4) = "tbl_Cases_Notes"
MyArray(5) = "tbl_Documents"
MyArray(6) = "tbl_Providers"
MyArray(7) = "tbl_ProviderNotes"
MyArray(8) = "tbl_ProviderContacts"
MyArray(9) = "tbl_Solicitors"
MyArray(10) = "tbl_ASU"
MyArray(11) = "tbl_BuildingsContents"
MyArray(12) = "tbl_FIB"
MyArray(13) = "tbl_Investments"
MyArray(14) = "tbl_Life"
MyArray(15) = "tbl_Mortgages"
MyArray(16) = "tbl_PHI"

wrkDefault.BeginTrans

DoCmd.SetWarnings False

'Delete records
For i = 16 To 0 Step -1
strSQL = "Delete * FROM " & MyArray(i) & " WHERE [AgentCode] = '" &
strImportAgentCode & "';"
DoCmd.RunSQL strSQL
Next i

DoCmd.SetWarnings True

If MsgBox("Save all changes?", vbYesNo) = vbYes Then
wrkDefault.CommitTrans
Else
wrkDefault.Rollback
End If
 
B

boblarson

The problem, I believe is that DoCmd.RunSQL is outside of the workspace and
therefore is not counted as being valid for the transaction. It isn't
associated with the transaction so it can't be rolled back or anything like
that.

To use it correctly, you would need to open a Db object and Recordset object
and use the Recordset object to do run the query.

--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.
 
D

Douglas J. Steele

Actually, since they're Delete queries, you can't use a recordset. Use the
Execute method of the database object.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


boblarson said:
The problem, I believe is that DoCmd.RunSQL is outside of the workspace
and
therefore is not counted as being valid for the transaction. It isn't
associated with the transaction so it can't be rolled back or anything
like
that.

To use it correctly, you would need to open a Db object and Recordset
object
and use the Recordset object to do run the query.


Jim Franklin said:
Hi,

I have tried using a transaction to make sure that changes to a number of
tables are completed successfully, or all rolled back. However, I cannot
get
the transaction to rollback, and the changes are applied regardless.

My test code below deletes all the records from a series of tables. Even
when I select No at the end, all my tables end up empty!

Any help is very much appreciated!

Cheers,
Jim

'set workspace for transaction
Set wrkDefault = DBEngine.Workspaces(0)

MyArray(0) = "tbl_Users"
MyArray(1) = "tbl_Applicants"
MyArray(2) = "tbl_Cases"
MyArray(3) = "tbl_CaseApplicants"
MyArray(4) = "tbl_Cases_Notes"
MyArray(5) = "tbl_Documents"
MyArray(6) = "tbl_Providers"
MyArray(7) = "tbl_ProviderNotes"
MyArray(8) = "tbl_ProviderContacts"
MyArray(9) = "tbl_Solicitors"
MyArray(10) = "tbl_ASU"
MyArray(11) = "tbl_BuildingsContents"
MyArray(12) = "tbl_FIB"
MyArray(13) = "tbl_Investments"
MyArray(14) = "tbl_Life"
MyArray(15) = "tbl_Mortgages"
MyArray(16) = "tbl_PHI"

wrkDefault.BeginTrans

DoCmd.SetWarnings False

'Delete records
For i = 16 To 0 Step -1
strSQL = "Delete * FROM " & MyArray(i) & " WHERE [AgentCode] = '"
&
strImportAgentCode & "';"
DoCmd.RunSQL strSQL
Next i

DoCmd.SetWarnings True

If MsgBox("Save all changes?", vbYesNo) = vbYes Then
wrkDefault.CommitTrans
Else
wrkDefault.Rollback
End If
Click to expand...
Click to expand...
 
J

Jim Franklin

Thanks Guys,

I must be missing something, but when you read the Microsoft help on
transactions, it gives the impression that ALL changes to the database since
the transaction began can be rolled back.

Does anyone know where I could read an accurate guide of what is associated
with the transaction and what isn't?

Thanks again,
Jim


Douglas J. Steele said:
Actually, since they're Delete queries, you can't use a recordset. Use the
Execute method of the database object.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


boblarson said:
The problem, I believe is that DoCmd.RunSQL is outside of the workspace
and
therefore is not counted as being valid for the transaction. It isn't
associated with the transaction so it can't be rolled back or anything
like
that.

To use it correctly, you would need to open a Db object and Recordset
object
and use the Recordset object to do run the query.


Jim Franklin said:
Hi,

I have tried using a transaction to make sure that changes to a number
of
tables are completed successfully, or all rolled back. However, I cannot
get
the transaction to rollback, and the changes are applied regardless.

My test code below deletes all the records from a series of tables. Even
when I select No at the end, all my tables end up empty!

Any help is very much appreciated!

Cheers,
Jim

'set workspace for transaction
Set wrkDefault = DBEngine.Workspaces(0)

MyArray(0) = "tbl_Users"
MyArray(1) = "tbl_Applicants"
MyArray(2) = "tbl_Cases"
MyArray(3) = "tbl_CaseApplicants"
MyArray(4) = "tbl_Cases_Notes"
MyArray(5) = "tbl_Documents"
MyArray(6) = "tbl_Providers"
MyArray(7) = "tbl_ProviderNotes"
MyArray(8) = "tbl_ProviderContacts"
MyArray(9) = "tbl_Solicitors"
MyArray(10) = "tbl_ASU"
MyArray(11) = "tbl_BuildingsContents"
MyArray(12) = "tbl_FIB"
MyArray(13) = "tbl_Investments"
MyArray(14) = "tbl_Life"
MyArray(15) = "tbl_Mortgages"
MyArray(16) = "tbl_PHI"

wrkDefault.BeginTrans

DoCmd.SetWarnings False

'Delete records
For i = 16 To 0 Step -1
strSQL = "Delete * FROM " & MyArray(i) & " WHERE [AgentCode] =
'" &
strImportAgentCode & "';"
DoCmd.RunSQL strSQL
Next i

DoCmd.SetWarnings True

If MsgBox("Save all changes?", vbYesNo) = vbYes Then
wrkDefault.CommitTrans
Else
wrkDefault.Rollback
End If
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

If you look at http://msdn2.microsoft.com/en-us/library/bb243155.aspx,
you'll see that it says "The transaction methods BeginTrans, CommitTrans,
and Rollback manage transaction processing during a session defined by a
Workspace object."

The problem with using RunSQL is that it's not associated with the Workspace
object you've instantiated: it just uses a default instance.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jim Franklin said:
Thanks Guys,

I must be missing something, but when you read the Microsoft help on
transactions, it gives the impression that ALL changes to the database
since the transaction began can be rolled back.

Does anyone know where I could read an accurate guide of what is
associated with the transaction and what isn't?

Thanks again,
Jim


Douglas J. Steele said:
Actually, since they're Delete queries, you can't use a recordset. Use
the Execute method of the database object.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


boblarson said:
The problem, I believe is that DoCmd.RunSQL is outside of the workspace
and
therefore is not counted as being valid for the transaction. It isn't
associated with the transaction so it can't be rolled back or anything
like
that.

To use it correctly, you would need to open a Db object and Recordset
object
and use the Recordset object to do run the query.


:

Hi,

I have tried using a transaction to make sure that changes to a number
of
tables are completed successfully, or all rolled back. However, I
cannot get
the transaction to rollback, and the changes are applied regardless.

My test code below deletes all the records from a series of tables.
Even
when I select No at the end, all my tables end up empty!

Any help is very much appreciated!

Cheers,
Jim

'set workspace for transaction
Set wrkDefault = DBEngine.Workspaces(0)

MyArray(0) = "tbl_Users"
MyArray(1) = "tbl_Applicants"
MyArray(2) = "tbl_Cases"
MyArray(3) = "tbl_CaseApplicants"
MyArray(4) = "tbl_Cases_Notes"
MyArray(5) = "tbl_Documents"
MyArray(6) = "tbl_Providers"
MyArray(7) = "tbl_ProviderNotes"
MyArray(8) = "tbl_ProviderContacts"
MyArray(9) = "tbl_Solicitors"
MyArray(10) = "tbl_ASU"
MyArray(11) = "tbl_BuildingsContents"
MyArray(12) = "tbl_FIB"
MyArray(13) = "tbl_Investments"
MyArray(14) = "tbl_Life"
MyArray(15) = "tbl_Mortgages"
MyArray(16) = "tbl_PHI"

wrkDefault.BeginTrans

DoCmd.SetWarnings False

'Delete records
For i = 16 To 0 Step -1
strSQL = "Delete * FROM " & MyArray(i) & " WHERE [AgentCode] =
'" &
strImportAgentCode & "';"
DoCmd.RunSQL strSQL
Next i

DoCmd.SetWarnings True

If MsgBox("Save all changes?", vbYesNo) = vbYes Then
wrkDefault.CommitTrans
Else
wrkDefault.Rollback
End If
Click to expand...
Click to expand...
Click to expand...
 
Top