Transaction in Access VBA?


I have an Access application using Linked Oracle table, when users click submit button in the form, there are two steps needs to be done (call a stored proc and call a SQL statement to update another table), if either of them failed, then the program needs to roll back. Could anyone let me know in Access VBA, what's the common and easy way to implement this kind of Transaction control? Thanks.
heyday2004Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Hello heyday2004,

I wouldn't try to set a transaction from Access.  Instead, build your sproc in Oracle, including appropriate
transaction handling, and have the sproc commit or roll back the transaction as needed.  Then, all you
need to do is issue a command via ADO to Orace to execute the sproc.

Regards,

Patrick
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
If you lookup BeginTrans in the vba Help file, you will find the three methods you need:

BeginTrans, CommitTrans and Rollback Methods with some examples.  It actually works quite well.  I currently have an import routine that runs about 15 queries under a transaction ... and if *anything* fails,  the entire operation IS rolledback ... which I have tested.

mx
0
Patrick MatthewsCommented:
MX,

No doubt it works--I'm just thinking that it would go more efficiently to push the burden to the Oracle DB server :)

Regards,

Patrick
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well, I can't speak for Oracle but you are probably right.  I was mainly pointing out how you could ... do transactions in Access ... if it made sense.

mx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.