BeginTrans - Rollback problem

[Jim Bunton]

I have a serious problem with BeginTrans - Rollback
Rollback isn't working

Below is the code for a command button on a test form on a test table
in a big complicated FromtEnd access 97 application
after the code is run the table has a new record in it!

The form + code work as expexted if dragged into a new database

Have tried:- Repair,Compact - no go!


ANY IDEAS PLEASE?

you might try an Access VBA newgroup

########## CODE ########
Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
Dim cdb As Database, testset As Recordset

BeginTrans
Set cdb = CurrentDb
Set testset = cdb.OpenRecordset("xxTest", DB_OPEN_DYNASET)
testset.AddNew
testset!egno = 1
testset.Update
Rollback
Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

###### END CODE #########

 

[Allen Browne]

In your code, the BeginTrans is not applied to anything. Apply it to the
default workspace.

There's an example and discussion of the pitfalls for transactions in this
article:
Archive: Move records to another table
at:
http://members.iinet.net.au/~allenbrowne/ser-37.html

 

[Jim Bunton]

Thanks for the reply Allen - no go I'm afraid

***** New code ****
Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim cdb As Database, testset As Recordset
MsgBox "Number of workspaces - " & Workspaces.Count
'workspaces.count returns 1
Workspaces(0).BeginTrans
Set cdb = CurrentDb
Set testset = cdb.OpenRecordset("xxTest", DB_OPEN_DYNASET)
testset.AddNew
testset!egno = 1
testset.Update
Workspaces(0).Rollback
Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click
End Sub
**** End Code ******

 

[Allen Browne]

You are not being consistent here.
Explicitly set a variable to the default workspace.
Begin the transaction.
Set your database variable to the databaes in that workspace.
OpenRecordset on that database variable.
Perform your changes.
You can now rollback.

Dim ws As DAO.Workspace
Set ws = DBEngine(0)
ws.BeginTrans
Set cdb = ws(0)
'Open your recordset here.

ws.Rollback

Go back and re-read the article. It explains all that.

 

[Jim Bunton]

Aha - seem to have found a possible answer:-

Dragged the old database bit by bit into a new empty database.

Compile began to throw up errors not found in thew original:
I had some code that was refering to columns in forms' RecordSource
columns which were NOT actually displayed as
me.columnName

altering these to Me!Column name

got rid of the compile errors and BeginTrans > RollBack is still working

SO - it's an fault in the compiler not picking these errors up in the
original database - PERHAPS!

 

[Allen Browne]

That sounds like a familiar inconsistency.

If you refer to a field in the form's recordset that is not represented by a
control on the form with the same name, the reference is an object of type
AccessField. This type has some inconsistencies (bugs?) associated with it.
Of of those is that it Me.MyField can suddenly stop compiling one day when
you make some other seemingly unrelated change. The workaround is (as you
found), to use Me!MyField, which is not checked at compile time (i.e. you
get no compiler error if you misspell it, like you do with Me.MyField). This
happens infrequently, but often enough that I now use the bang whenver I
refer to an AccessField.

 

[Jim Bunton]

Thanks for the help Allen -
I shall follow your example in future and make sure that I use the bangs!