?
Solved

Why doesn't rollback work?

Posted on 2003-03-05
6
Medium Priority
?
736 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

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!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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 Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

771 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