Transactions - No transaction when rollback

[campbell_canuck]

Hello and TIA,

I am trying to write some VB code to move some data between 2 Access
databases using a code module in Access. As part of it, I am trying to
use transactions.

However, by the time I get to the commit or rollback statements it no
longer recognizes the transaction. What am I doing wrong?

on error goto errhandler
CurrentProject.Connection.BeginTrans
CurrentProject.Connection.Execute "update checklist_item set comment =
'transaction test' where checklist_item_id = 5127"
' CurrentProject.Connection.CommitTrans
CurrentProject.Connection.RollbackTrans

errhandler:
MsgBox Error(err.number)

the message I get is always the same.
"You have tried to commit or rollback a transaction without first
beginning a transaction"

[John Mishefske]

campbell_canuck wrote:

Hello and TIA,

I am trying to write some VB code to move some data between 2 Access
databases using a code module in Access. As part of it, I am trying to
use transactions.

However, by the time I get to the commit or rollback statements it no
longer recognizes the transaction. What am I doing wrong?

on error goto errhandler
CurrentProject.Connection.BeginTrans
CurrentProject.Connection.Execute "update checklist_item set comment =
'transaction test' where checklist_item_id = 5127"
' CurrentProject.Connection.CommitTrans
CurrentProject.Connection.RollbackTrans

errhandler:
MsgBox Error(err.number)

the message I get is always the same.
"You have tried to commit or rollback a transaction without first
beginning a transaction"

What happens if you cache the object reference to the Connection?

on error goto errhandler

With CurrentProject.Connection
.BeginTrans
.Execute "update checklist_item set comment =
'transaction test' where checklist_item_id = 5127"
' .CommitTrans
.RollbackTrans
End With

errhandler:
MsgBox Error(err.number)
--
'---------------
'John Mishefske
'---------------

[Tom van Stiphout]

On 4 May 2006 11:03:32 -0700, "campbell_canuck"
<ch*******@hotmail.com> wrote:

What is the point of a transaction around a single update statement?
-Tom.

Hello and TIA,

I am trying to write some VB code to move some data between 2 Access
databases using a code module in Access. As part of it, I am trying to
use transactions.

However, by the time I get to the commit or rollback statements it no
longer recognizes the transaction. What am I doing wrong?

on error goto errhandler
CurrentProject.Connection.BeginTrans
CurrentProject.Connection.Execute "update checklist_item set comment =
'transaction test' where checklist_item_id = 5127"
' CurrentProject.Connection.CommitTrans
CurrentProject.Connection.RollbackTrans

errhandler:
MsgBox Error(err.number)

the message I get is always the same.
"You have tried to commit or rollback a transaction without first
beginning a transaction"