dbFailOnError? (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 00:07
Joined
Sep 21, 2011
Messages
14,048
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. :confused:

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

Ignore List Poster Boy
Local time
Yesterday, 20:07
Joined
Apr 27, 2015
Messages
6,286
When have you EVER know Allen Browne to be wrong?
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:07
Joined
Sep 21, 2011
Messages
14,048

NauticalGent

Ignore List Poster Boy
Local time
Yesterday, 20:07
Joined
Apr 27, 2015
Messages
6,286
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...
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 00:07
Joined
Sep 12, 2006
Messages
15,614
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

MVP / VIP
Local time
Today, 00:07
Joined
Jan 14, 2017
Messages
18,186
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!
 

Micron

AWF VIP
Local time
Yesterday, 20:07
Joined
Oct 20, 2018
Messages
3,476
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.
 

Users who are viewing this thread

Top Bottom