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.  
  
--  
  
Sylvain Lafontaine, ing.  
  
MVP - Technologies Virtual-PC  
  
E-mail: http://cerbermail.com/?QugbLEWINF  
  
"Herb" <Herb@discussions.microsoft.com> wrote in message  
  
news:9C115AA5-5169-407C-8D2E-FC61B1C806C6@microsoft.com...

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  
  
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>  
  
escreveu na mensagem news:OUGVpL2IGHA.1728@TK2MSFTNGP09.phx.gbl...  
  
that  
  
SQL-Server,  
  
for  
  
function;  
  
suffer  
  
a  
  
connection

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