CurrentDB, DBEngine and Transactions

hey guys,

ok i know there is a huge debate about currentdb and dbengine and frankly i don't have the bandwidth to go through it now though i really, really, really need to.

question here is that let's say i've got 2 code blocks

Code Block 1:
start transaction
   currentdb.execute SQL
rollback transaction

Code Block 2:
set db = currentdb
start transaction
   db.execute SQL
rollback transaction

when i rollback for Code Block 1, will it only roll back the last execute statement cause the currentdb is a new instance? i think Code Block 2 is the safe way to do it right?

i would do some testing myself to find this out but i don't have the knowledge right now to understand the results of the testing comprehensively - rather i would be forming personal theories about this without reading through the whole currentdb vs dbengine debate, so apologies guys i'm asking this question here - and also i think yall have a lot more experience in this and surely able to point out blindspots i'd have missed. thanks guys!! = ))
developingprogrammerAsked:
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.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
In the examples you gave, both would result in the same thing.

Transactions occur at the workspace level and hinge on when you use the start trans and commit and rollback commands.

Jim.
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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I should add that CurrentDB() vs dbEngine is actually a seperate issue.  

More on point is the fact that for some opertations, Access maintains it's own internal workspace object.

Transactions are/were intended for multi-table updates you do in code with recordsets and *nothing* beyond that.

Jim.
0
Dale FyeCommented:
Jim,

"Transactions are/were intended for multi-table updates you do in code with recordsets and *nothing* beyond that."

Was that really the only intention?  I know the Access 2000 Developers Handbook has a section on creating way to rollback changes on a form and its subform(s), which is all code based, but was that really the only intent?  BTW, I think I got this to work once, but it is a bit complicated.

Other relational databases have transaction processing designed for use with action queries, I cannot imagine that the Access development teams sole purpose was to allow commit/rollback "updates you do in code with recordsets".
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< I cannot imagine that the Access development teams sole purpose was to allow commit/rollback "updates you do in code with recordsets". >>

<<Was that really the only intention? >>

 Initially yes.   Transactions have existed for quite some time,  in fact all the way back to Access 1.x.

  They were intended for controlling multi-table updates within code.  1.x though had the three coresponding statements (not methods) in Access Basic and they only worked within workspace(0).

 Access keeps it's own internal workspace object for certain things, like form recordsets.   It wasn't up until A2000 that you could bind a form to a recordset opened in code and hence be able to use transactions with a form.

 But again, they only work with things you do in code.   While similar to transaction processing in other RDBM's like SQL Server, they are not quite the same thing.  There is no option for controlling them at the engine level, nor on a database by database basis.  They also may not apply to all recordsets.

Jim.
0
Dale FyeCommented:
I must admit, I rarely use transactions unless I'm performing a sequence of action queries, where it essential that they all complete properly, or none do.  That works well for me.  I've tried to do it with recordsets, and never got it to work, but didn't put much effort into it.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"I should add that CurrentDB() vs dbEngine is actually a seperate issue.  "

And lets not go there in this Question :-)
Just - Say - No !

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"I must admit, I rarely use transactions unless "
Humm. I'm just the opposite. And I've never had a problem getting transactions to work.

The most complex trans I have here at work is ... one that is run during the daily loading of one of the back ends on the server. There are 17 operations that are run under a trans - all or none.  And of course this is done over the 1Gb WAN.  I believe I've posted a screen shot of this before.
I have fully tested this many times - forcing errors, etc. It works very well.  It's the only reliable way to execute this process.
0
developingprogrammerAuthor Commented:
thanks for the clarification Jim, fyed and mx! this is an area i definitely have to beef up my knowledge on. thanks once again = )
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.