Coomit/Rollback

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everibody,
my problem is to obtain the transactions (atomicity in particular) on a VBA access 2000 applicaton.
For that purpoose I tried the functionality on a demo appication (v. http://support.microsoft.com/?kbid=248011).
In that demo, both the begin and the end of the transaction are on the same form, and it works.
Vice-versa on my application I neeed to involve more than one single form in the same transaction; but this is the problem that I couldn't solve.
I need is to open the transaction on the calling form, and to assure the commit/rollback functionality on all the chain of calls to subforms at each level of call (because the subforms operate on the DB).
It seems that the commit/rollback are possible only on the same form where the beginTrans is.......
Can someone help me?
Thanks in advice
Luigi
 
Hi Luigi.

Your conclusions are right I think. I have never been able to get a rollback
working reliably on both a form and its subform within the same transaction.

If anyone else has succeeded, we are all interested in hearing, but I have
never achieved it or seen it done.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Louis said:
my problem is to obtain the transactions (atomicity in particular) on a VBA access 2000 applicaton.
For that purpoose I tried the functionality on a demo appication (v. http://support.microsoft.com/?kbid=248011).
In that demo, both the begin and the end of the transaction are on the same form, and it works.
Vice-versa on my application I neeed to involve more than one single form
Click to expand...
in the same transaction; but this is the problem that I couldn't solve.
I need is to open the transaction on the calling form, and to assure the
Click to expand...
commit/rollback functionality on all the chain of calls to subforms at each
level of call (because the subforms operate on the DB).
 
And of course, you can't even wrap a "manual" db update in the underlying
transaction of a single form - where no subforms are involved. The workspace
that is used by each form, is not exposed to VBA.

TC
 
Not sure I followed that TC.

In Access 2000 and later, it is possible to open a transaction, open a
recordset within that transaction, and set the RecordSet of a form to that
transaction so it's changes are cached inside the transaction. The concept
cannot be extended to subforms AFAIK.
 
Ah, ok. I was thinking of A97, where (as you doubtless know), you can not
base a form on a recordset, and the transaction used by the form is not
available to VBA.

So in A2k, you can't base the form on one recordset, & the subform on
another, both off the same local workspace, & wrap all the changes (main &
subform) in one transaction?

Cheers,
TC
(off for the day)
 
Nah: the linking between main form and subform doesn't work properly if you
try that.
 
I got this to work. At first the rollback didn't, but then when I adde
the code below it worked (of course change the table name):
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM Customers"
dbOpenDynaset)
Set Me.Recordset = rs

Works great, only bad thing is it doesn't work if you have a subfor
where data is changing also... like mine
 
Back
Top