msaccess transactions

[ultimatewilliam]

is it possible to implement transactions in msaccess? hope someone could tell me where to start. thanks.

 

[CCLINT]

See the DAO (DBEngine) or ADO(Connection object) BeginTrans, CommitTrans and RollBackTrans methods.

 

[BBaggins]

If you're using DAO, begin/commit/rollback are methods of the workspace object.

Be aware that transactions are more limited in Access than in many other databases such as SQL Server. You cannot roll back a record deletion or DDL, for example.

 

[ultimatewilliam]

thanks bbaggins, but i'm actually using ADO and its alright if i can't rollback record deletion.

 

[CCLINT]

>You cannot roll back a record deletion or DDL, for example.

Ohh, generally speaking, yes you can....

 

[ultimatewilliam]

ok, so where do i start?

 

[CCLINT]

Are you using MSACCESS, or are you using VB6 and have a connection to a JET/MSACCESS Mdb?

If the latter, then I would suggest going over to the MSACCESS modules forum and asking there how to create recordsets, work with a database object and implement a transaction.

If the former, then continue here and let us know what you want to use a transaction for.

 

[leiphasw]

Assuming you have ADO code that is working and you just want to add transactions, just add the BeginTrans and CommitTrans method invocations in your code on the connection object doing the data access. Put the BeginTrans before your database modifications and the CommitTrans after the last Update. If there are errors and you want to roll back everything since the BeginTrans, then put a RollbackTrans in the error handler. The RollbackTrans method removes all the modifications to the data since the last BeginTrans.

Additional notes:

You can do modifications to any tables/queries in the database that are accessed by that connection object and all the modifications to all the tables/queries are committed or rolled back together. This is actually the big strength of the transactions; to keep multi-table updates in sync. If one table update fails, you won't want the other table that is related to succeed and have "dangling" table entries.

Also, Transactions are sort of "time sensitive". If you have a MsgBox or open another form while a transaction is "open", it will commit automatically and you won't be able to roll it back. Since the transaction is based on the image of the database at the moment the transaction starts, it will lock the affected rows in all the tables until the commit. It won't hold these locks, for example, while the user reads a MsgBox to make some decision. You also can't single step through the code debugging in a transaction and expect a roll back at the end to work correctly. As soon as a breakpoint is hit, the transaction commits.

Here is a mini example for a connection called gDBConn:

================

< I have two recordset variables RS1 and RS2 that
point to two tables I want to keep synchronized. >
< Get and validate some data off a form >

on error goto baggit

gDBConn.BeginTrans

RS1.AddNew
RS1(&quot;MemberName&quot = tMemberName
RS1(&quot;MemberSince&quot = now
RS1.Update

RS2.AddNew
RS2(&quot;MemberID&quot = RS1(&quot;ID&quot
RS2(&quot;ProductID&quot = tProductID
RS2(&quot;Price&quot = tPrice
RS2.Update

gDBConn.CommitTrans

Exit Sub

baggit:

gDBConn.RollbackTrans

Exit Sub

================

This code will only update the tables if BOTH tables can be updated successfully. If either update fails, neither table is affected.

Hope this helps,
Steve.

 

[CCLINT]

>If you have a MsgBox or open another form while a transaction is &quot;open&quot;, it will commit automatically and you won't be able to roll it back
>As soon as a breakpoint is hit, the transaction commits

I may be un-aware of something here, but I must ask...Is this true working in the ACCESS enviroment, or what?
Because, I do not see where this is true in VB, with ADO, unless a time-out occures....

 

[ultimatewilliam]

i just found out that it's not access that deals with the transactional part of the db work, it is ADO that does it. thank you all.

 

[CCLINT]

>it is ADO that does it

No...it is the provider: JET
ADO doesn't perform any transactions itself.

Don't confuss MSACCESS with JET.
MSACCESS is just a user interface.
JET is the dbms for a MDB.

 

[ultimatewilliam]

ok, whatever.

 

[johnwm]

ultimatewilliam
That's a rather ungracious remark to make to someone who has spent a lot of time and effort to help you resolve your problem, and clear up an obvious misunderstanding on your part.

I think he deserves better!

We all come here to help and be helped as we can. See faq222-2244 for etiquette, and we all look forward to your contributions to other members questions.


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[ultimatewilliam]

sorry guys, i didn't mean to hurt anyone here. i got too excited to solve my problem