CommitTrans in Forms

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

Guest

I have an MSAccess form in "continuous forms" mode that is bound to a table.
Records can be added, deleted and modified within the form but there is a
command box that allows the user to CANCEL.

Is there a way to use BeginTrans, ComitTrans and Rollback to accomplish this?

From what I've read about this feature it has to be based on a connection
object and the form does not really open a connection object that I can see.

Any help would be greatly appreciated.

Thank you
 
In the same way as with MDB files, Access makes its own use of the
transactions for bound forms and bound tables and trying to add a level of
transaction above that is strongly discouraged by Microsoft.

You should use transactions only for you own procedures and recordsets that
you manipulate directly and that are not bound to a form.

By default, an Access project will open three connections to the SQL-Server,
the first one is used to populate the GUI while the other two are used for
populating bound forms. Furthermore, Access can open more connections for
populating subforms or performing various tasks such as making a DLookup
call. Trying to handle this within your own transactions will probably
result in a failure. See http://support.microsoft.com/kb/308312/EN-US/ for
more informations on connections opened by ADP.

Finally, you should never use Transactions to accomplish a Cancel function;
they have not been created for this purpose and your application will suffer
from scaling and peformance degradation problems if you do so.
 
Hi

Asked the same question my self some time ago and got this answer.
Not possible, but doable.
Use unbound Forms and build your own transaction model.
how ?
on opening the form, just replicate tha data to a local table.
do whatever you want, logging the actions on the form, writing to a temp
table UPDATE, DELETE, INSERT and the relevant data.
then, on close, update,delete or insert the data on the remote server and
wipe out the temptable. if on a multiuser environment, phew ... man, you got
some work to do.......
hit cancel, just close the form with no updates and wipe out the temp table
issues, don't use autonumber or autoincrease identity fields.
very slow on finish, hard to implement, multiuser is a mess, working with
sub tables is an awsome workload, good as an academic exercise.
not much of help and gave up myself.
if anyone knows a practical way of doing this, please help, as i still want
to implement the transactions on forms.

Have a nice weeked
 
Hello Herb,
You wrote in conference microsoft.public.access.adp.sqlserver on Fri, 27
Jan 2006 08:09:02 -0800:

H> I have an MSAccess form in "continuous forms" mode that is bound to a
H> table. Records can be added, deleted and modified within the form but
H> there is a command box that allows the user to CANCEL.

H> Is there a way to use BeginTrans, ComitTrans and Rollback to accomplish
H> this?

These methods won't work; however, currentproject.connection.execute "begin
transaction" etc. will work as you want. Be careful though, because Access
works with client-based cursors, which will be then "wrapped" in the
transaction. Also keep in mind the issue of several connections that Sylvain
has mentioned - be careful with populated comboboxes and such. But the main
controls on the form will abide.

Vadim Rapp
 
Back
Top