DBEngine .Execute Method Fail Scenarios

  • Thread starter Thread starter dch3
  • Start date Start date
D

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.
 
You get a trappable error in the Errors collection.

Err.Number is the last one.
Loop through them if are are multiple items.
 
What sorts of thigns would cause .Execute to crap out?
Click to expand...

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!
 
Do you need to use dbFailOnError to get an error for insert
or update violations?

(david)
 
Do you need to use dbFailOnError to get an error for insert
or update violations?
Click to expand...

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.
 
David W. Fenton said:
(if you know you're appending a recordset that has
duplicates that you know for certain you want to discard),
Click to expand...

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/
 
circumstances (if you know you're appending a recordset that has
duplicates that you know for certain you want to discard), but is
Click to expand...

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

(david)
 
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.
Click to expand...

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.
 
Which is a bad idea because the process is slow and causes
database bloat.
Click to expand...

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.
 
david said:
Which is a bad idea because the process is slow and causes database bloat.
Click to expand...

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/
 
And it consumes autonumbers. Which is not a big deal nevertheless
I have some pride in my craftmanship. <smile>
Click to expand...

Er, how would appending duplicate PKs consume Autonumbers if the PK
is the Autonumber field?
 
David W. Fenton said:
Er, how would appending duplicate PKs consume Autonumbers if the PK
is the Autonumber field?
Click to expand...

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/
 
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 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.
Click to expand...

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?
 
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.
Click to expand...

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).
 
David W. Fenton said:
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.
Click to expand...

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?
Click to expand...

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/
 
Back
Top