Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Why doesn't rollback work?

Posted on 2003-03-05
6
Medium Priority
?
750 Views
Last Modified: 2008-02-07
Trying to use Rollback in VBA, but any written data persists after rollback.

Here is a little test program I am trying to get working. It runs to completion without any errors, but I end up with a new row in the table:
Sub doit()
    Dim listRS As New Recordset
   
    DBEngine.Workspaces(0).BeginTrans
    listRS.Open "[testtable]", CurrentProject.Connection, adOpenKeyset, adLockPessimistic
    listRS.AddNew
    listRS![y] = 69
    listRS![z] = "stuff"
    listRS.Update
   
    DBEngine.Workspaces(0).Rollback
End Sub

Notes:
I am using Access 2000
I have created a brand new empty database with one simple table and this one bit of code (So no issues with migration from Access 97 and earlier).
I have tried using CurrentProject.Connection.BeginTrans and CurrentProject.Connection.RollbackTrans, but I get the error "you tried to commit or rollback without first beginning a transaction".
I also tried specifying "ADODB" for the recordset, as in "Dim listRS As New ADODB.Recordset", but this didn't seem to make any difference at all.
Is this a bug in Access, or am I doing something wrong?
0
Comment
Question by:tearinghairout
6 Comments
 
LVL 5

Expert Comment

by:bob_online
ID: 8072753
you need to execute the rollback an not the update.  The update commits your changes -- there is no longer anything to rollback.
0
 
LVL 3

Expert Comment

by:LordThlan
ID: 8073470
If you rollback a transaction it will undo the update, that is the purpose of the transaction.  Looking on the msdn website, it uses the following names:

BeginTrans, CommitTrans, RollbackTrans

Here is the link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdamth01_4.asp
0
 
LVL 3

Expert Comment

by:Koka1
ID: 8074882
Try to use:
CurrentProject.Connection.BeginTrans
...
CurrentProject.Connection.RollbackTrans

IMHO problem is that You use DAO workspace transaction methods while updating ADO recordset.
Good luck
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Accepted Solution

by:
ripnasty earned 300 total points
ID: 8075444
Your current code doesn't work because you have an ADO recordset but Workspaces is a DAO method.

Using CurrentProject.connection directly in your statements won't work (as you've discovered) because referring to CurrentProject.connection twice doesn't necessarily refer to the same instance (hence your "trying to rollback without starting a transaction" error).

So... add the following:

  Dim cnn1 As ADODB.Connection
  Set cnn1 = CurrentProject.Connection
 

When you open the recordset, replace "CurrentProject.Connection" with cnn1

And use the following for your transaction handling:

cnn1.BeginTrans
...
cnn1.RollbackTrans
...
cnn1.CommitTrans

And everything will work because you've captured the connection instance to cnn1.
0
 
LVL 1

Expert Comment

by:ripnasty
ID: 8075468
Oh... and I'm not sure what bob_online is talking about.  The whole point of transaction handling is so you can undo your changes!!  Nothing has happened yet until you do the .update so running a rollback before the update is kinda pointless.
0
 

Author Comment

by:tearinghairout
ID: 8078549
Thanks ripnasty
Not only the correct answer, but a clear explanation as to why it's the correct answer.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question