dbFailOnError?

[Gasman]

Ok knowledgeable people,

In responding to this thread post #8, I linked to a MS page on how to check the subject line

https://docs.microsoft.com/en-us/of...atabase-reference/database-execute-method-dao

There it states it rolls back any updates.

Then in another Google link I found something by Allen Browne

https://bytes.com/topic/access/answers/199471-how-verify-sql-update-succeeded

where he states that it does not.

He then goes on to mention further in the thread

How you missed it, David, was that it DID roll back in Access 95 and
earlier. Microsoft mulitated it in Access 97 without documenting the change
in the help file: you had to read the readme to find out!!!

So which is it? I must admit I just *assumed* the former.:-(

TIA

 

[NauticalGent]

When have you EVER know Allen Browne to be wrong?

 

[Gasman]

When have you EVER know Allen Browne to be wrong?

Well further googling produced this

https://www.experts-exchange.com/questions/25120536/What-does-dbFailOnError-do.html

I must admit the chances of myself having to use this now are quite remote, but I would still like to know.

There is mention of DAO in this, I *think* ADO is now the default, so perhaps that has something to do with it.?

 

[NauticalGent]

Good article on EE, although some ruffled fearthers occured - some folks can get really wrapped around the axle!

There is mention of DAO in this, I *think* ADO is now the default, so perhaps that has something to do with it.?

Actually, I think it is the other way around these days with DAO being the default. What I did not know is that for a complete "roll back" to occur, the dbFailOnError bit had to be within a Transaction.

Very informative read...

 

[AccessBlaster]

When have you EVER know Allen Browne to be wrong?

LOl @ NauticalGent

 

[gemma-the-husky]

I thought, that if you run a query by

docmd.openquery then
- if you suppress results (ie setwarnings falsr) the query runs but not necessariiy completely, and you don't know what errors there were.
- if you don't suppress results (ie setwarnings true) then you see the results and can confirm whether you want to run the query or not

if you do currentdb.execute with dbfailonerror. then I thought that the query failed if there were any execution errors - eg syntax errors, or query process errors. I just tested it, and that's what happened - it either all works, or doesn't work at all, unlike the docmd.openquery syntax.

 

[isladogs]

Dave
Agree with all you wrote, but we're talking about a locked record rather than a code error.

I ran some tests on this yesterday as I wasn't convinced Allen Browne was correct in this case..

In my tests, on several occasions using dbFailOnError the update statement was rolled back when a record was locked. At other times it wasn't.

I tested repeatedly but was unable to determine any clear rules as to why it sometimes allowed the update but at other times rolled back the changes.

At best, the outcome is unreliable!

 

[Deleted member 147267]

https://docs.microsoft.com/en-us/off...ute-method-dao
There it states it rolls back any updates.

I'm confused by that statement. The way I read it, it does not roll back anymore.

In earlier versions of the Microsoft Jet database engine, SQL statements were automatically embedded in implicit transactions. If part of a statement executed with dbFailOnError failed, the entire statement would be rolled back. To improve performance, these implicit transactions were removed starting with version 3.5. If you are updating older DAO code, be sure to consider using explicit transactions around Execute statements.