DBEngine .Execute Method Fail Scenarios

[dch3]

I'm trying to figure out scenarious where the .Execute method of a database
(DAO) object would fail.

Also, when it does fail, is the error trapped by the usual Err object or
another object. I vaguely recall that you have to look at a different object
for the specific error information.

 

[Allen Browne]

You get a trappable error in the Errors collection.

Err.Number is the last one.
Loop through them if are are multiple items.

 

[dch3]

What sorts of thigns would cause .Execute to crap out?

 

[David W. Fenton]

What sorts of thigns would cause .Execute to crap out?

With an INSERT, for instance, attempting to attend values that
violate field validation rules, or that create index violations. Or
with an UPDATE, changing the values such that they do either of
those things. Or running the SQL against a table that is locked
exclusively.

Or any of a hundred other things that could happen!

 

[david]

Do you need to use dbFailOnError to get an error for insert
or update violations?

(david)

 

[Tony Toews [MVP]]

Do you need to use dbFailOnError to get an error for insert
or update violations?

Yes, you should always be using dbFailOnError.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

Do you need to use dbFailOnError to get an error for insert
or update violations?

If you don't use dbFailOnError, you have no idea what will happen.
That is, with it off, it will complete the updates it can and
silently fail the others. That *can* be useful in certain
circumstances (if you know you're appending a recordset that has
duplicates that you know for certain you want to discard), but is
most often *not* a good thing.

In other words, *not* using dbFailOnError is like answering YES to
the prompt that comes up when you run an action query that produces
errors, except without a notification of what the errors are.

 

[Tony Toews [MVP]]

(if you know you're appending a recordset that has
duplicates that you know for certain you want to discard),

Whereas I strongly suspect you and I would go to great lengths to not
add those duplicates in the first place so we wouldn't get the
message.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[david]

circumstances (if you know you're appending a recordset that has

duplicates that you know for certain you want to discard), but is

Which is a bad idea because the process is slow and causes database bloat.

(david)

 

[David W. Fenton]

Whereas I strongly suspect you and I would go to great lengths to
not add those duplicates in the first place so we wouldn't get the
message.

That's correct. I'd use an outer join to eliminate the dupes,
because I fear what happens when you ignore errors, given that there
might be unexpected errors other than the ones you planned for.

 

[David W. Fenton]

Which is a bad idea because the process is slow and causes
database bloat.

Well, if you did it in a transaction, it wouldn't bloat the back
end.

As Tony observes, I don't do this kind of thing, because I'm afraid
that I'd possibly be discarding errors other than the ones I assume
would be happening.

 

[Tony Toews [MVP]]

Which is a bad idea because the process is slow and causes database bloat.

And it consumes autonumbers. Which is not a big deal nevertheless I
have some pride in my craftmanship. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

And it consumes autonumbers. Which is not a big deal nevertheless
I have some pride in my craftmanship. <smile>

Er, how would appending duplicate PKs consume Autonumbers if the PK
is the Autonumber field?

 

[Tony Toews [MVP]]

Er, how would appending duplicate PKs consume Autonumbers if the PK
is the Autonumber field?

I don't see anywhere in this thread where PKs were mentioned. I don't
ever recall, other than when importing data from other database which
I'm discarding, that I've ever inserted PKs into a table where the PK
was an autonumber. Therefore I made that assumption.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[dch3]

So the long and the short of it is that the rule about closing what you open
and destroying what you create applies here as well - check what you .Execute.

 

[David W. Fenton]

I don't see anywhere in this thread where PKs were mentioned. I
don't ever recall, other than when importing data from other
database which I'm discarding, that I've ever inserted PKs into a
table where the PK was an autonumber. Therefore I made that
assumption.

Well, for the duplicates to be discarded, there has to be an unique
index. The PK would be the obvious unique key. If it's a surrogate
key then there might be another unique index, yes.

I'm not sure why anyone would *worry* about consuming Autonumber
values, though. Is it not the case that we are always giving the
advice that one shouldn't be worrying about the values in an
Autonumber field?

 

[David W. Fenton]

So the long and the short of it is that the rule about closing
what you open and destroying what you create applies here as well
- check what you .Execute.

I was trying to come up with a plausible scenario where *not* using
dbFailOnError would have utility.

Personally, I'd never do it, but that doesn't mean it's not a
*plausible* scenario (if not advisable).

 

[Tony Toews [MVP]]

Well, for the duplicates to be discarded, there has to be an unique
index. The PK would be the obvious unique key. If it's a surrogate
key then there might be another unique index, yes.

The only time, in my experience, you'd need to be inserting rows with
an autonumber PK value is if doing conversions from other systems.

I'm not sure why anyone would *worry* about consuming Autonumber
values, though. Is it not the case that we are always giving the
advice that one shouldn't be worrying about the values in an
Autonumber field?

Yes, you are correct. However I still don't like consuming them if I
didn't have to. As well you get the other issues such as bloating
database and so forth.

TOny
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/