Transactions with VBA

  • Thread starter Thread starter wieczo.yo
  • Start date Start date
W

wieczo.yo

Hello,

I want to delete and update records of different tables and when
there's an error, nothing should be done. I read about
ADODB.Connection.BeginTrans and .CommitTrans and .RollbackTrans. I am
not sure how to use them. How do I know if a transaction was
successful?


Thomas Wieczorek

P.S.: I am using Access2k and Access2k3
 
Thomas,

I have to ask. Where did you read about transactions? I can't remember ever
seeing a book, magazine, or help article that didn't include some sample
code. Did you experiment with the code that was with the article you read?
There is no better way to learn code techniques than to step through sample
code.

That said, why are you looking at ADO? If you are using Jet (mdb file)
tables, you should be using DAO. DAO was created for Jet. Although ADO can
be used with Jet, it was designed for non-Jet data sources like SQL Server,
Oracle, MySQL, etc. In fact, you'll find that DAO transactions have
capabilities that ADO transactions don't have.

To answer your question about how you know a transaction was successful, you
don't. A transaction doesn't succeed or fail. A transaction is a wrapper
around individual actions (update, delete, append, etc.) that succeed or
fail. The transaction wrapper allows you to commit the changes or restore
(rollback) the data. You typically use error trapping to determine if an
operation in the transaction failed.

I have several examples of running update queries in transactions in my
November 2005 presentation that I make to the Denver Area Access Users
group. You can get the files from my website at http://www.scobiz.com/. Look
for presentations on the left side. I started the presentation using
DoCmd.OpenQuery which can not be used in transactions and then I progress to
examples of using database.Execute. Some of the examples succeed with a
commit and some fail with a rollback. You will find a Word document in the
download that has instructions on how to run the demos.

Although the demos update different fields in the same table, the techniques
work the same using different tables.

The sample database is in 2000 format, so it will work in Access 2000, 2002,
and 2003 with no modifications.

I suggest you spend some time studying the transaction keyword sections in
the DAO help file.

Good luck.

Sco

M.L. "Sco" Scofield, MCSD, MCP, MSS, A+, ex-MVP
Denver Area Access Users Group 2006 President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #19 of 19: 2 wharves = 1 paradox
Miscellaneous Access and VB "stuff" at www.ScoBiz.com
 
Hello Sco,
I read about transactions in the Access VBA help and I tried the
example in the help file. I am using ADO because the whole project is
using ADO. I also heard that DAO is the old model and that Microsoft
suggests to use ADO and that DAO is only included in the newer versions
of Access because of compatibility to older versions.
The next day after the post, I found out that you have to use "On Error
Goto label" to use the rollback.

Thank you for your explenations, they really helped me to understand
more.

Regards, Thomas
 
Back
Top