BeginTrans . .

  • Thread starter Thread starter Jim Bunton
  • Start date Start date
J

Jim Bunton

I'm having a bit of an issue with
BeginTrans - CommitTrans
. . . .
Often used it before mainly in Access 97 ! with no problem
But now - Access 2000 (wndows XP)
using References
Visual Basic For Applications
Access 9.0 Object Library
OLE Automation
Microsift DAO 3.5 Object Library
Microsoft ActiceX Data Objects 2.1Library

Begin Trans CommitTrans doesn't seem to be working as I expect.
The code below for example updates the table!

**code start**
BeginTrans
testSet.Edit
testSet!flat1 = 999
testSet.Update
Rollback
** Code End **
 
Jim -

One thought... Does the recordset (testSet) have the Transactions property
as True? Not all recordsets can use transaction processing. - if the
property is False, then you can't use Transactions on this recordset.
 
I haven't written DAO transactions for a while, but I thought the
..BeginTrans was a method on the Workspace object? I seem to remember
starting the procedure by getting an explicit reference to a Workspace,
starting the transaction, and referencing the workspace object for all the
data modifications.

Here's an example from the online help:
Dim wrkCurrent As DAO.Workspace
Dim dbsNorthwind As DAO.Database
Dim rstEmployee As DAO.Recordset

On Error GoTo ErrorHandler

Set wrkCurrent = DBEngine.Workspaces(0)
Set dbsNorthwind = CurrentDB
Set rstEmployee = dbsNorthwind.OpenRecordset("Employees")

wrkCurrent.BeginTrans
Do Until rstEmployee.EOF
If rstEmployee!Title = "Sales Representative" Then
rstEmployee.Edit
rstEmloyee!Title = "Sales Associate"
rstEmployee.Update
End If
rstEmployee.MoveNext
Loop

If MsgBox("Save all changes?", vbQuestion + vbYesNo) = vbYes Then
wrkCurrent.CommitTrans
Else
wrkCurrent.Rollback
End If
 
Dear Daryl & Paul,
Thanks for responding to my posting
I'm still having the same issue!
see code below - I must say I'm just mystified!
Note I'm using Microsoft DAO 3.5 Object Library because I'm more used to the
syntax and being a bit dinosaur like I guess

Jim

** code **
Public Sub TestTransaction()
Dim cdb As DAO.Database, set1 As DAO.Recordset
Dim myspace As DAO.Workspace

Set myspace = DBEngine.Workspaces(0)
'Debug.Print currentdb.Name 'works fine but remmed for security
'Msjet40.dll is version 4.0.9511.0

Set cdb = currentdb
Set set1 = cdb.OpenRecordset("Table1", DB_OPEN_DYNASET)
Debug.Print set1.Properties(14).Name, set1.Properties(14)
set1.Edit
set1!Flat1 = 900
set1.Update
myspace.BeginTrans
Debug.Print "StartValue " & set1!Flat1
set1.Edit
set1!Flat1 = 100
set1.Update
myspace.Rollback
Debug.Print "EndValue - after rollback! " & set1!Flat1

End Sub
** code end **

Code output!
Transactions True
StartValue 900
EndValue - after rollback! 900
 
Jim -

With the code you have, I would expect the answer to be 900 since you rolled
back the transaction setting it to 100, and the update to 900 happened before
the BeginTrans.

What isn't working?
 
Daryl
Yes! you're quite right.

Moral of tale - don't rush to get something done before eeagerly anticipated
lunch!

When I checked (after much apprciated lunch) it does indeed now work aok

The crucial bit is to create a workspace object AND use it as the prefix
before Begintrans and Rollback
i.e myspace.BeginTrans works - just Begintrans DOES NOT

[The use of the DAO. prefix in the Dimension statements is not required but
maybe a cautonary tale here - use it in future anyway in case there are
other issues which may arise.

Thanks for your help - and patience. AND a BIG sorry for my error before
lunch.

Jim
 
Jim -
Glad it is working. Sometimes we just need a break and a little fuel to
regain our perspective and see the issue more clearly!

--
Daryl S


Jim Bunton said:
Daryl
Yes! you're quite right.

Moral of tale - don't rush to get something done before eeagerly anticipated
lunch!

When I checked (after much apprciated lunch) it does indeed now work aok

The crucial bit is to create a workspace object AND use it as the prefix
before Begintrans and Rollback
i.e myspace.BeginTrans works - just Begintrans DOES NOT

[The use of the DAO. prefix in the Dimension statements is not required but
maybe a cautonary tale here - use it in future anyway in case there are
other issues which may arise.

Thanks for your help - and patience. AND a BIG sorry for my error before
lunch.

Jim


Daryl S said:
Jim -

With the code you have, I would expect the answer to be 900 since you
rolled
back the transaction setting it to 100, and the update to 900 happened
before
the BeginTrans.

What isn't working?
Click to expand...


.
Click to expand...
 
Back
Top