dbFailOnError: When might it make sense NOT to include this? (1 Viewer)

Isaac

Lifelong Learner
Local time
Today, 04:52
Joined
Mar 14, 2017
Messages
8,738
I have some CurrentDb.Execute.............dbFailonError statements throughout my database. They are things I run administratively, not by end users nor part of any deployed database/system.

They update sharepoint list fields. Occasionally, when the sharepoint list is busy, I'll get "cannot update - database or object is read only". I think that the root cause of this is that I am trying to update fields that the Infopath/sharepoint form is also trying to update, or something close to that along those lines.

In these cases, I assume that Microsoft Access is most likely able to update a number of records........Perhaps a few hundred out of the total = 1000 expected.

Let's say I am OK with that - I'd like Access to update whatever it can, and "skip over" the ones that it failed on, OR, (at worst), I'd like it to update whatever it can, and fail at the point when it gets to the one it can't update........But go ahead and commit any updates done up to then.

Would this be an appropriate time to leave off dbFailonError?
If so, what if I still want to KNOW that the failure happened, would Access still be expected to tell me? Would I need to use a currentdb-set variable and then check recordsaffected vs. records expected - would that be the only way to know?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:52
Joined
Oct 29, 2018
Messages
21,358
Although I haven't really used it much, I wonder if this is a situation where one can use Transactions. I know most situations that use Transactions typically roll back the entire recordset, but I wonder if you can force a commit instead.

Also, perhaps another approach is to loop through a recordset and process each record one at a time. You can then decide what to do with failed updates and also create your own counters.
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 04:52
Joined
Mar 14, 2017
Messages
8,738
Great ideas. I think I used dao transactions once, and it was mostly to try to teach it to myself in test! Good ideas both ... thank you.

I think I might go with the second, at first, because I also seek insight into just how fast [slow] this thing is going per record or what the heck is going on.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:52
Joined
Oct 29, 2018
Messages
21,358
Great ideas. I think I used dao transactions once, and it was mostly to try to teach it to myself in test! Good ideas both ... thank you.

I think I might go with the second, at first, because I also seek insight into just how fast [slow] this thing is going per record or what the heck is going on.
Good luck and please let us know how it goes. Cheers!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:52
Joined
Feb 19, 2013
Messages
16,555
Let's say I am OK with that - I'd like Access to update whatever it can, and "skip over" the ones that it failed on, OR, (at worst), I'd like it to update whatever it can, and fail at the point when it gets to the one it can't update........But go ahead and commit any updates done up to then.

May be heavy handed but being very used to transferring data to/from disparate systems I use a metatable to store all relevant limits (e.g. datatype, number range, date range, text size, required,etc). I then run a query (dynamically built in vba based on the metatable) on the source to test for compliance. The query generates a list on non compliant rows and gives the user the opportunity to abort the entire import, import the compliant records or import all but substitute null for the non compliant fields. These opportunities are based on user rights - so some users have not choice but to abort.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:52
Joined
Feb 28, 2001
Messages
27,001
My take on that question is this: Does the update represent something that is repeatable in your business model? A dbFailOnError implies a single-query rollback if the query goes whacko on you. If the rollback occurs then you can simply repeat the query. Might be a little tedious but, as they say in pick-up basketball - no harm, no foul.

But what if you didn't use the dbFailOnError option and the update didn't hit every record? (You could tell with .RecordsAffected, perhaps.) If the thing being updated was a flag based on conditions and if the conditions remained in effect for the records that failed, you could repeat the query and just set the flags again, even if they were already set. Again, that is a "no harm, no foul" case.

BUT if you were updating values like a counter of some kind, would you be able to repeat the query (from a procedural viewpoint) if it was partially updated and not rolled back? Like the simple update of UPDATE table SET tcount = tcount + 1 WHERE tfield = "X" ; If you did that and it partly failed, you would have a heck of a time knowing which records were incremented and which ones were not. OK, it is a contrived example for discussion purposes and an example of bad programming to do that particular step that particular way. Used for illustrative purposes anyway. But the (rhetorical) question remains... is the query something that could safely be repeated if a prior run was incomplete due to file locking or buffer locking or some other unpredictable issue?
 

Users who are viewing this thread

Top Bottom