Vanishing Joins

[TC]

I've used Access for many years. Several times, I've encountered a bug
which I refer to as the "Vanishing Joins" bug. When it happens, joins
vanish randomly from queries. More specifically, all joins vanish from
at least one (seemingly random) query.

I've always regarded the Vanishing Joins bug as a symptom of
corruption. When it happens, I usually give my users advice on how to
recover from corruption, and how to avoid it in the future. It isn't
always resolved that easily, however. On one occasion, vanishing joins
were responsible for a very bad failure of one of my applications.
Because of vanishing joins in a source query, a report looked correct
but contained errors. The erroneous report was used for a while, and
when the errors were finally discovered, I lost credibility as a
developer.

Because of that experience, I've concluded that the Vanishing Joins
bug is especially insidious. When the bug occurs, the database shifts
into a faulty mode without warning, and produces errors and/or bad
data that may go unnoticed for a long time. It is this bug, more than
any other, which forces me to advise users that Access is simply not
good enough for some purposes.

Although it is insidious, the Vanishing Joins bug is thankfully rare.
Nevertheless, I encountered it again last week, and that's what led me
to think about it now. To deal with it once and for all, I'm thinking
about creating a table that stores a hash of each query's SQL. Then,
on startup, I'll verify the hash. That will provide a warning when the
bug occurs, and I should be able to implement it as a general solution
that can be added to all my applications. Before I start work on it,
however, I want to touch base with the Access community and see what
others think about this issue.

Because it has happened to me several times, I assumed this was a well-
known bug. After searching, however, I found only one mention of it
(http://access.softwareheadlines.com/...s-in-frontend-
t72397.html). As a reality check, therefore, I'd like to ask: Are
other developers aware of the this bug? Do you have different ideas
about it than the ones I've presented here? And, most importantly, has
anyone found an easy way to deal with it?
-TC

[JvC]

I would bet that you have Name AutoCorrect turned on. First thing to do
is go to Tools, Options, General Tab, and turn it off. I suspect it is
what is killing (fixing?) the joins. If at any time you delete one of
the members of the join, and then replace it, Access, in it's infinate
wisdom, kills the join when the member is deleted, and doesn't replace
it when you reimport the object.

Remember, it's not a bug, it's a feature!

John

TC has brought this to us :

I've used Access for many years. Several times, I've encountered a bug
which I refer to as the "Vanishing Joins" bug. When it happens, joins
vanish randomly from queries. More specifically, all joins vanish from
at least one (seemingly random) query.

I've always regarded the Vanishing Joins bug as a symptom of
corruption. When it happens, I usually give my users advice on how to
recover from corruption, and how to avoid it in the future. It isn't
always resolved that easily, however. On one occasion, vanishing joins
were responsible for a very bad failure of one of my applications.
Because of vanishing joins in a source query, a report looked correct
but contained errors. The erroneous report was used for a while, and
when the errors were finally discovered, I lost credibility as a
developer.

Because of that experience, I've concluded that the Vanishing Joins
bug is especially insidious. When the bug occurs, the database shifts
into a faulty mode without warning, and produces errors and/or bad
data that may go unnoticed for a long time. It is this bug, more than
any other, which forces me to advise users that Access is simply not
good enough for some purposes.

Although it is insidious, the Vanishing Joins bug is thankfully rare.
Nevertheless, I encountered it again last week, and that's what led me
to think about it now. To deal with it once and for all, I'm thinking
about creating a table that stores a hash of each query's SQL. Then,
on startup, I'll verify the hash. That will provide a warning when the
bug occurs, and I should be able to implement it as a general solution
that can be added to all my applications. Before I start work on it,
however, I want to touch base with the Access community and see what
others think about this issue.

Because it has happened to me several times, I assumed this was a well-
known bug. After searching, however, I found only one mention of it
(http://access.softwareheadlines.com/...s-in-frontend-
t72397.html). As a reality check, therefore, I'd like to ask: Are
other developers aware of the this bug? Do you have different ideas
about it than the ones I've presented here? And, most importantly, has
anyone found an easy way to deal with it?
-TC

[Allen Browne]

Name AutoCorrect was my first thought. Clearly Jeannette Cunningham had the
same idea. You did not indicate whether you have the Name AutoCorrect
options turned off in your front end/back end.

Weird things can happen if you open the query in design view while the back
end is not available. (Access 2007 handles this issue better than older
versions do.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"TC" <go*********@yahoo.comwrote in message
news:01**********************************@w7g2000h sa.googlegroups.com...

I've used Access for many years. Several times, I've encountered a bug
which I refer to as the "Vanishing Joins" bug. When it happens, joins
vanish randomly from queries. More specifically, all joins vanish from
at least one (seemingly random) query.

I've always regarded the Vanishing Joins bug as a symptom of
corruption. When it happens, I usually give my users advice on how to
recover from corruption, and how to avoid it in the future. It isn't
always resolved that easily, however. On one occasion, vanishing joins
were responsible for a very bad failure of one of my applications.
Because of vanishing joins in a source query, a report looked correct
but contained errors. The erroneous report was used for a while, and
when the errors were finally discovered, I lost credibility as a
developer.

Because of that experience, I've concluded that the Vanishing Joins
bug is especially insidious. When the bug occurs, the database shifts
into a faulty mode without warning, and produces errors and/or bad
data that may go unnoticed for a long time. It is this bug, more than
any other, which forces me to advise users that Access is simply not
good enough for some purposes.

Although it is insidious, the Vanishing Joins bug is thankfully rare.
Nevertheless, I encountered it again last week, and that's what led me
to think about it now. To deal with it once and for all, I'm thinking
about creating a table that stores a hash of each query's SQL. Then,
on startup, I'll verify the hash. That will provide a warning when the
bug occurs, and I should be able to implement it as a general solution
that can be added to all my applications. Before I start work on it,
however, I want to touch base with the Access community and see what
others think about this issue.

Because it has happened to me several times, I assumed this was a well-
known bug. After searching, however, I found only one mention of it
(http://access.softwareheadlines.com/...s-in-frontend-
t72397.html). As a reality check, therefore, I'd like to ask: Are
other developers aware of the this bug? Do you have different ideas
about it than the ones I've presented here? And, most importantly, has
anyone found an easy way to deal with it?
-TC

[Albert D. Kallal]

"TC" <go*********@yahoo.comwrote in message
news:01**********************************@w7g2000h sa.googlegroups.com...

Because it has happened to me several times, I assumed this was a well-
known bug.

Actually, most of us have never encountered this problem, and your
difficulty in
finding info for your problem proves this.

After searching, however, I found only one mention of it

Exactly, it not a problem that normal and proficient developers encounter on
any type of regular bases.

Are
other developers aware of the this bug?

I was not aware of it, and never encountered it, and that is the case for
most of us.

Do you have different ideas
about it than the ones I've presented here?

I never had the problem in 10+ years of development, and with about 20,000
posts here a month, that would represent 2,400,000 posts, and it is not
normally mentioned in the newsgroups either.

In other words it is such a insignificant rare occurring event that normal
competent people would not (in fact have not!) given this issue a
2nd thought, or spent more time then what has been used up in this post.

So, for the most part few
if anyone would give this issue one hoot because no one really having this
problem on any type of repeating or regular bases.

And, most importantly, has
anyone found an easy way to deal with it?

I don't think anyone found a way to deal with something that is not
effecting
them. As mentioned, about the only suggesting that came up was to turn off
track-autoname correct.

We should perhaps go through the standard list of trouble shooting
things.

do you:

track name autocorrect turned off.
is a network involved?
is your application multi-user
is your application split
do you distribute a mde to your users?
have you installed updates and bugs fixes to access?
have you installed update and bug fixes to jet?
I am certainly not suggesting that this problem don't exist, nor is there
some lack of skills at play here, but the issue is such a rare occurrence
you not likely to find anyone who can give you help on something that few if
anyone is having a problem with.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[rumkus]

"...aware of the this bug? "

No, never. Unless - as JvC put it

".. If at any time you delete one of
the members of the join, and then replace it "

[Tony Toews [MVP]]

"Chris O'C via AccessMonster.com" <u29189@uwewrote:

Dim db As Database
Set db = Currentdb
db.Execute "action query", dbFailOnError
Set db = Nothing

Unless I need to know the number of records updated I just use

currentdb.execute etc, etc

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/

[rumkus]

" SetWarnings should never be
turned off, DoCmd.RunSQL should never be used, and On Error Resume
Next
should be avoided "

Thank God Chris is not one of those who pay me for my poor vba codes.
And them people are only interested in correct and timely reports.
Strange though those codes have never failed me so far.
I mean I smoke and enjoy it very much :)

[Tony Toews [MVP]]

TC <go*********@yahoo.comwrote:

>I've used Access for many years. Several times, I've encountered a bug
which I refer to as the "Vanishing Joins" bug. When it happens, joins
vanish randomly from queries. More specifically, all joins vanish from
at least one (seemingly random) query.

The only time I've seen this is when I had to email updated queries to a client. I
would put the changed objects into a new MDB and email them to the client. If the
"holding" mdb containing just the changed objects didn't have the linked tables the
queries involved would all lose all their joins.

Other than that I haven't seen this.

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/

[lyle fairfield]

I must be totally out to lunch with this. A query, whatever else we may
think it is, is fundamentally an sql string. Is it something else? What
am I missing? So how could the string change? I suppose people have been
using that silly query wizard/dialog, whatever you want to call it, to
create saved queries? But surely no real developer would trust his/her
query strings to that nonsense? It's inefficient, SUPER ugly and error
prone. Regardless, is that the place from which the joins disappear? So
there are applications that RELY on these things? Why would you do that
when you can be in control by coding your own query strings? You want a
compiled query because it's faster? I don't THINK THO!

A developer using saved queries created by the know-nothing MS query
design helper? How quaint!

I must be missing something. I guess I said that already?

"Tony Toews [MVP]" <tt****@telusplanet.netwrote in
news:7v********************************@4ax.com:

The only time I've seen this is when I had to email updated queries to
a client. I would put the changed objects into a new MDB and email
them to the client. If the "holding" mdb containing just the changed
objects didn't have the linked tables the queries involved would all
lose all their joins.

[lyle fairfield]

Many things amaze me. But the posts here that say really stupid things or
ask really stupid questions and also talk about "my clients" are at the
top of the heap.

Oh well, I was looking at some legacy data for which a client paid big
bucks just on Friday.
Oh I said, this doesn't follow what one would normally expect in the way
of atmocity.
Oh I said, this doesn't follow what one would normally expect in the way
of unique identification.

Here are two records that have the same identifier. They represent two
entities.
Here are two other records that have the same identifier. They represent
one entity.
HUH? How's a guy/gal to know? What? "Phone the site?" You're kidding me,
right? Well, no they weren't kidding.

Well, I know the dollar rules all but does no one have any pride; can you
sleep at night after you take money for a pile of crap?

Yeah, I know, I'm outta touch with reality.
"ru****@hotmail.com" <ru****@hotmail.comwrote in news:cf23fa7a-3aea-
47********************@m36g2000hse.googlegroups.co m:

" SetWarnings should never be
turned off, DoCmd.RunSQL should never be used, and On Error Resume
Next
should be avoided "

Thank God Chris is not one of those who pay me for my poor vba codes.
And them people are only interested in correct and timely reports.
Strange though those codes have never failed me so far.
I mean I smoke and enjoy it very much :)

[JvC]

Lyle,

Try an experiment.
Create a front end database with two attached tables. Call it db1
Create a query with a join between the two tables.
Create a second front end with Name AutoCorrect turned on that only has
one of the two tables attached. Call it db2.
Copy the query from db1 to db2.
Copy it back to db1.

The join is gone, and any of the fields from the missing tables are
aliased with Exprx:

lyle fairfield presented :

I must be totally out to lunch with this. A query, whatever else we may
think it is, is fundamentally an sql string. Is it something else? What
am I missing? So how could the string change? I suppose people have been
using that silly query wizard/dialog, whatever you want to call it, to
create saved queries? But surely no real developer would trust his/her
query strings to that nonsense? It's inefficient, SUPER ugly and error
prone. Regardless, is that the place from which the joins disappear? So
there are applications that RELY on these things? Why would you do that
when you can be in control by coding your own query strings? You want a
compiled query because it's faster? I don't THINK THO!

A developer using saved queries created by the know-nothing MS query
design helper? How quaint!

I must be missing something. I guess I said that already?

"Tony Toews [MVP]" <tt****@telusplanet.netwrote in
news:7v********************************@4ax.com:

>The only time I've seen this is when I had to email updated queries to
a client. I would put the changed objects into a new MDB and email
them to the client. If the "holding" mdb containing just the changed
objects didn't have the linked tables the queries involved would all
lose all their joins.

[lyle fairfield]

I suppose idiocy can produce or destroy anything. Since the query would be
invalid in db2 who could be surprised if the "join" disappeared?

As for AutoCorrect I have never turned it off, nor have I ever turned it
on, nor have I ever paid any attention to it at all, that I can remember.
It has zapped me, TTBOMR, zero times. I have worked with every version of
Access.

Almost every bug that is reported here is not a bug; almost every bug is is
Access/VBA/JET or Ace struggling with stupidity, laziness, carelessness,
lack of knowledge or experience, all coupled with misplaced arrogance.

I have arrogance too but it is not misplaced; I know where it is at all
times; it is right here on my shoulder.

JvC <jo******@earthlink.netwrote in
news:OT******************@newsfe04.iad:

Lyle,

Try an experiment.
Create a front end database with two attached tables. Call it db1
Create a query with a join between the two tables.
Create a second front end with Name AutoCorrect turned on that only
has one of the two tables attached. Call it db2.
Copy the query from db1 to db2.
Copy it back to db1.

The join is gone, and any of the fields from the missing tables are
aliased with Exprx:

[JvC]

Lyle,

You posed a question:
"I must be totally out to lunch with this. A query, whatever else we
may think it is, is fundamentally an sql string. Is it something else?
What am I missing? So how could the string change?"

I simply answered it.

lyle fairfield explained on 9/7/2008 :

I suppose idiocy can produce or destroy anything. Since the query would be
invalid in db2 who could be surprised if the "join" disappeared?

As for AutoCorrect I have never turned it off, nor have I ever turned it
on, nor have I ever paid any attention to it at all, that I can remember.
It has zapped me, TTBOMR, zero times. I have worked with every version of
Access.

Almost every bug that is reported here is not a bug; almost every bug is is
Access/VBA/JET or Ace struggling with stupidity, laziness, carelessness,
lack of knowledge or experience, all coupled with misplaced arrogance.

I have arrogance too but it is not misplaced; I know where it is at all
times; it is right here on my shoulder.

JvC <jo******@earthlink.netwrote in
news:OT******************@newsfe04.iad:

>Lyle,

Try an experiment.
Create a front end database with two attached tables. Call it db1
Create a query with a join between the two tables.
Create a second front end with Name AutoCorrect turned on that only
has one of the two tables attached. Call it db2.
Copy the query from db1 to db2.
Copy it back to db1.

The join is gone, and any of the fields from the missing tables are
aliased with Exprx:

[lyle fairfield]

Thank you for explaining how the string might change.
If this is the only sequence of steps that can cause the string to change
then I think I am supported in my position that this is not a bug.

JvC <jo******@earthlink.netwrote in
news:Vk*****************@newsfe04.iad:

Lyle,

You posed a question:
"I must be totally out to lunch with this. A query, whatever else we
may think it is, is fundamentally an sql string. Is it something else?
What am I missing? So how could the string change?"

I simply answered it.

[JvC]

It is absolutely NOT a bug, it is a change in the behavior of Access.
This initially came to my attention with some software I wrote that I
use to update client systems. With Access 95, 97 and 2000, there were
never problems. With 2003, the queries without the underlying
tables/subqueries were treated as more than just a string, and the
joins broke. Time to redo the updater!

lyle fairfield has brought this to us :

Thank you for explaining how the string might change.
If this is the only sequence of steps that can cause the string to change
then I think I am supported in my position that this is not a bug.

JvC <jo******@earthlink.netwrote in
news:Vk*****************@newsfe04.iad:

>Lyle,

You posed a question:
"I must be totally out to lunch with this. A query, whatever else we
may think it is, is fundamentally an sql string. Is it something else?
What am I missing? So how could the string change?"

I simply answered it.

[rkc]

On Sep 7, 8:50*pm, JvC <[e-mail]:

It is absolutely NOT a bug, it is a change in the behavior of Access.
This initially came to my attention with some software I wrote that I
use to update client systems. With Access 95, 97 and 2000, there were
never problems. With 2003, the queries without the underlying
tables/subqueries were treated as more than just a string, and the
joins broke. Time to redo the updater!

I think Mr. FairField's point is that if you do not use the
capabilities that Access
provides for designing and saving queries that can then simply be
referenced by
name you won't ever see the problem. If your queries are built in
code or
stored in tables or textfiles then Access will obviously never change
them no
matter what tables are present or not present. They may not work, but
they
won't be mysteriously changed.

His implied point that people who do use saved queries are less than
competent
is interesting. I'm not buying it though.

[David W. Fenton]

JvC <jo******@earthlink.netwrote in
news:OT******************@newsfe04.iad:

Try an experiment.
Create a front end database with two attached tables. Call it db1
Create a query with a join between the two tables.
Create a second front end with Name AutoCorrect turned on

Er, why would anyone bother to perform an experiment that is doomed
by the user of Name AutoCorrect? Turn it off -- it's worthless!

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Tony Toews [MVP]]

lyle fairfield <ly******@yah00.cawrote:

>I suppose people have been
using that silly query wizard/dialog, whatever you want to call it, to
create saved queries? But surely no real developer would trust his/her
query strings to that nonsense? It's inefficient, SUPER ugly and error
prone.

Yes, I do that all the time. It's fast, convenient, works and is an efficient use of
my time.

>Regardless, is that the place from which the joins disappear?

Yes.

>So
there are applications that RELY on these things? Why would you do that
when you can be in control by coding your own query strings? You want a
compiled query because it's faster? I don't THINK THO!

A developer using saved queries created by the know-nothing MS query
design helper? How quaint!

All the time. And I'm quite happy with them.

quaint 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/

[Tony Toews [MVP]]

"ru****@hotmail.com" <ru****@hotmail.comwrote:

>" SetWarnings should never be
turned off, DoCmd.RunSQL should never be used, and On Error Resume
Next
should be avoided "

Thank God Chris is not one of those who pay me for my poor vba codes.
And them people are only interested in correct and timely reports.
Strange though those codes have never failed me so far.
I mean I smoke and enjoy it very much :)

Chris is correct.

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/

[rkc]

On Sep 7, 11:36*pm, "Tony Toews [MVP]" <[e-mail]:

"[e-mail]" <[e-mail]:

" SetWarnings should never be
turned off, DoCmd.RunSQL should never be used, and On Error Resume
Next
should be avoided "

Thank God Chris is not one of those who pay me for my poor vba codes.
And them people are only interested in correct and timely reports.
Strange though those codes have never failed me so far.
I mean I smoke and enjoy it very much :)

Chris is correct.

His opinion of On Error Resume Next throws an error.

[rumkus]

Well, I know the dollar rules all
- No idea about dollar's ruling

but does no one have any pride

- Who knows ? Probably.

>can you sleep at night after you take money for a pile of crap ?

- I'd better reserve my words for holidays.

>Yeah, I know, I'm outta touch with reality.

- too sleepy to jump onto the wrong pillow to be more precise.

[Tony Toews [MVP]]

rkc <rk*@rkcny.comwrote:

>" SetWarnings should never be
turned off, DoCmd.RunSQL should never be used, and On Error Resume
Next
should be avoided "

>Thank God Chris is not one of those who pay me for my poor vba codes.
And them people are only interested in correct and timely reports.
Strange though those codes have never failed me so far.
I mean I smoke and enjoy it very much :)

Chris is correct.

His opinion of On Error Resume Next throws an error.

The only situation where I use On Error Resume Next is where the next line is an If
err.number of Select case err.number. And I handle the possible error right there.
There are very few places I've done this. One place though is when renaming files in
a long routine such as backup. If there is an error I want to handle that error
right there rather than in a catchall at the bottom of the code.

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]

lyle fairfield <ly******@yah00.cawrote in
news:Xn**********************@216.221.81.119:

I must be totally out to lunch with this. A query, whatever else
we may think it is, is fundamentally an sql string.

In Jet, a saved query has a number of properties not accessible via
any UI provided by an MS product, chief among those, the optimized
query plan.

Secondly, certain kinds of SQL strings cannot be embedded in other
SQL strings in Jet SQL unless the query has been saved.

Is it something else? What
am I missing? So how could the string change?

When you save SQL in the QBE grid, it validates your SQL and
interprets it for you and disambiguates certain things (and
sometimes guesses wrong).

I suppose people have been
using that silly query wizard/dialog, whatever you want to call
it, to create saved queries? But surely no real developer would
trust his/her query strings to that nonsense?

Are you this stupid, or is this just a guise you put on to,
troll-like, rile up the reasonable people?

It's inefficient, SUPER ugly and error
prone.

Not if you're competent in using it.

? Regardless, is that the place from which the joins disappear?

Non-equi joins can disappear if you write them in SQL view and then
try to view them in query designer (which can't display them). But
anyone who is competent enough to write non-equi joins in SQL view
is also smart enough to remember not to try to view such a query in
design view (or, if they forget and screw up the SQL, they are smart
enough either to not save the change, or to rewrite the non-equi
join).

A developer using saved queries created by the know-nothing MS
query design helper?

Created by? Huh? None of my queries are created by the QBE. They are
created by *me*, utilizing the QBE as a tool. When that tool doesn't
do well, I dont' use it.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

"Tony Toews [MVP]" <tt****@telusplanet.netwrote in
news:6e********************************@4ax.com:

"ru****@hotmail.com" <ru****@hotmail.comwrote:

>>" SetWarnings should never be
turned off, DoCmd.RunSQL should never be used, and On Error Resume
Next
should be avoided "

Thank God Chris is not one of those who pay me for my poor vba
codes. And them people are only interested in correct and timely
reports. Strange though those codes have never failed me so far.
I mean I smoke and enjoy it very much :)

Chris is correct.

Indeed, Chris is, in fact, correct.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

rkc <rk*@rkcny.comwrote in
news:c2**********************************@l64g2000 hse.googlegroups.co
m:

On Sep 7, 11:36*pm, "Tony Toews [MVP]" <[e-mail]>
wrote:

>Chris is correct.

His opinion of On Error Resume Next throws an error.

The only reason to *ever* use On Error Resume Next is because you
anticipate a particular error and want to ignore it.

But that puts you in the position of also ignoring any error you
have *failed* to anticipate. This is *awful* code design.

If you anticipate an error and want to ignore it, trap for that
specific error and throw it away. That is the only sensible way to
write code that isn't going to cause mysterious problems down the
road.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[lyle fairfield]

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in
news:Xn*********************************@74.209.13 6.82:

In Jet, a saved query has a number of properties not accessible via
any UI provided by an MS product, chief among those, the optimized
query plan.

Is the optimized query plan accessible via some technology other than a UI
provided by an MS product?
What are the other properties not accessible via any UI provided by an MS
product?

Secondly, certain kinds of SQL strings cannot be embedded in other
SQL strings in Jet SQL unless the query has been saved.

Could you provide examples of non-embeddable SQL strings?

--
lyle fairfield

[rkc]

On Sep 9, 8:52*pm, "David W. Fenton" <[e-mail]>
wrote:

rkc <[e-mail] innews:c2**********************************@l64g20 00hse.googlegroups.co
m:

On Sep 7, 11:36*pm, "Tony Toews [MVP]" <[e-mail]>
wrote:

Chris is correct.

His opinion of On Error Resume Next throws an error.

The only reason to *ever* use On Error Resume Next is because you
anticipate a particular error and want to ignore it.

But that puts you in the position of also ignoring any error you
have *failed* to anticipate. This is *awful* code design.

If you anticipate an error and want to ignore it, trap for that
specific error and throw it away. That is the only sensible way to
write code that isn't going to cause mysterious problems down the
road.

Maybe I can learn more from you.
How much more verbose does this need to be?

Public Sub AddCriteria(criteria As ICriteria, key As Variant)
On Error Resume Next
colCriteria.Remove key
colCriteria.Add criteria, key
On Error GoTo 0
End Sub

[David W. Fenton]

rkc <rk*@rkcny.comwrote in
news:da**********************************@i76g2000 hsf.googlegroups.co
m:

On Sep 9, 8:52*pm, "David W. Fenton"
<[e-mail]:

>rkc <[e-mail]
innews:c2282c3e-0f75-4ee1-b5f4-49db211370e8@l6

4g2000hse.googlegroups.co

>m:

On Sep 7, 11:36*pm, "Tony Toews [MVP]" <[e-mail]>
wrote:
Chris is correct.

His opinion of On Error Resume Next throws an error.

The only reason to *ever* use On Error Resume Next is because you
anticipate a particular error and want to ignore it.

But that puts you in the position of also ignoring any error you
have *failed* to anticipate. This is *awful* code design.

If you anticipate an error and want to ignore it, trap for that
specific error and throw it away. That is the only sensible way
to write code that isn't going to cause mysterious problems down
the road.

Maybe I can learn more from you.
How much more verbose does this need to be?

Public Sub AddCriteria(criteria As ICriteria, key As Variant)
On Error Resume Next
colCriteria.Remove key
colCriteria.Add criteria, key
On Error GoTo 0
End Sub

It needs an error handler to trap whatever for whatever specific
error(s) you are intending to ignore. I don't know the error number
for attempting to remove an item from a collection when the key
doens't exist, but I assume that's what you want to ignore.

So, if the error were 8000 it would be this:

Public Sub AddCriteria(criteria As ICriteria, key As Variant)
On Error GoTo errHandler

colCriteria.Remove key
colCriteria.Add criteria, key

exitRoutine:
Exit Sub

errHandler:
Select Case Err.Number
Case 8000
On Error Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description, vbExclamation, _
"Error in AddCriteria()"
Resume exitRoutine
End Select
End Sub

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Carlos Nunes-Ueno]

I just got in the habit of always writing my error handlers in a form
similar to David's:

Select Case Err.Number
Case Else
'The default error handler goes here usually something like:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_FunctionName
End Select

If there is a particular error that needs to be handled in a special way,
then I'm just a Case statement away. If not, then I've typed a couple
extra lines. No big deal.

[rkc]

On Sep 10, 7:02*pm, "David W. Fenton" <[e-mail]>
wrote:

rkc <[e-mail] innews:da**********************************@i76g20 00hsf.googlegroups.co
m:

On Sep 9, 8:52*pm, "David W. Fenton"
<[e-mail]:

rkc <[e-mail]
innews:c2282c3e-0f75-4ee1-b5f4-49db211370e8@l6

4g2000hse.googlegroups.co

m:

On Sep 7, 11:36*pm, "Tony Toews [MVP]" <[e-mail]>
wrote:
Chris is correct.

His opinion of On Error Resume Next throws an error.

The only reason to *ever* use On Error Resume Next is because you
anticipate a particular error and want to ignore it.

But that puts you in the position of also ignoring any error you
have *failed* to anticipate. This is *awful* code design.

If you anticipate an error and want to ignore it, trap for that
specific error and throw it away. That is the only sensible way
to write code that isn't going to cause mysterious problems down
the road.

Maybe I can learn more from you.
How much more verbose does this need to be?

Public Sub AddCriteria(criteria As ICriteria, key As Variant)
* * On Error Resume Next
* * colCriteria.Remove key
* * colCriteria.Add criteria, key
* *On Error GoTo 0
End Sub

It needs an error handler to trap whatever for whatever specific
error(s) you are intending to ignore. I don't know the error number
for attempting to remove an item from a collection when the key
doens't exist, but I assume that's what you want to ignore.

So, if the error were 8000 it would be this:

*Public Sub AddCriteria(criteria As ICriteria, key As Variant)
*On Error GoTo errHandler

* *colCriteria.Remove key
* *colCriteria.Add criteria, key

*exitRoutine:
* *Exit Sub

*errHandler:
* *Select Case Err.Number
* * *Case 8000
* * * *On Error Resume Next
* * *Case Else
* * * *MsgBox Err.Number & ": " & Err.Description, vbExclamation,_
* * * * "Error in AddCriteria()"
* * * *Resume exitRoutine
* *End Select * *
*End Sub

That would be great except your placement of On Error Resume Next
would result in nothing
ever being added to the collection.

[David W. Fenton]

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in
news:Xn**********************************@74.209.1 36.93:

It needs an error handler to trap whatever for whatever specific
error(s) you are intending to ignore. I don't know the error
number for attempting to remove an item from a collection when the
key doens't exist, but I assume that's what you want to ignore.

So, if the error were 8000 it would be this:

Public Sub AddCriteria(criteria As ICriteria, key As Variant)
On Error GoTo errHandler

colCriteria.Remove key
colCriteria.Add criteria, key

exitRoutine:
Exit Sub

errHandler:
Select Case Err.Number
Case 8000
On Error Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description, vbExclamation,
_
"Error in AddCriteria()"
Resume exitRoutine
End Select
End Sub

I miscoded that in a rather dangerous way -- the error handler
should be:

errHandler:
Select Case Err.Number
Case 8000
Resume Next <==This is the changed line
Case Else
MsgBox Err.Number & ": " & Err.Description, vbExclamation, _
"Error in AddCriteria()"
Resume exitRoutine
End Select

But since posting, I've rethought this and have two better
alternatives. The first uses a GoTo:

Public Sub AddCriteria(criteria As ICriteria, key As Variant)
On Error GoTo errHandler

colCriteria.Remove key
errSkip:
colCriteria.Add criteria, key

exitRoutine:
Exit Sub

errHandler:
Select Case Err.Number
Case 8000
GoTo errSkip
Case Else
MsgBox Err.Number & ": " & Err.Description, vbExclamation, _
"Error in AddCriteria()"
Resume exitRoutine
End Select
End Sub

But I know people just hate GoTos, so another alternative would be
to do it differently, entirely, and that would be to just Add the
new item to the collection, and trap for the error of that key
already existing, Let's say the error number is 8001:

Public Sub AddCriteria(criteria As ICriteria, key As Variant)
On Error GoTo errHandler

errRetry:
colCriteria.Add criteria, key

exitRoutine:
Exit Sub

errHandler:
Select Case Err.Number
Case 8001
colCriteria.Remove key
GoTo errRetry
Case Else
MsgBox Err.Number & ": " & Err.Description, vbExclamation, _
"Error in AddCriteria()"
Resume exitRoutine
End Select
End Sub

But both of these actually violate one of my main principles --
don't raise an error that you've anticipated. Instead, check for
whether the error will be produced and then avoid doing something
that would create the error. Thus, you would do this:

Private Function bolKeyExists(mcolCollection As Collection, _
varValue As Variant) As Boolean
Dim varItem As Variant
Dim i As Integer
Dim ysnKeyExists As Boolean

For i = 1 To mcolCollection.Count
ysnKeyExists = (mcolCollection(i) = varValue)
If ysnKeyExits Then Exit For
Next i
bolKeyExists = ysnKeyExists
End Function

Then you'd use that function to test whether it needed to be added:

Public Sub AddCriteria(Criteria As ICriteria, varKey As Variant)
On Error GoTo errHandler

If Not (bolKeyExists(colCriteria, varKey) Then
colCriteria.Add Criteria, varKey
End If

exitRoutine:
Exit Sub

errHandler:
MsgBox Err.Number & ": " & Err.Description, vbExclamation, _
"Error in AddCriteria()"
Resume exitRoutine
End Sub

The point here is that you avoid raising an error. My philosophy is
that error handlers are expensive in terms of processing power, and
it's better to avoid raising an error that you have to trap for.
Now, the question is how expensive it is to walk a collection (as is
done in the bolKeyExists() function). This will depend, of course,
on how big the collection is, but I'm pretty sure it would take a
collection with thousands of items for a walk through the collection
to be slower than tripping the error handler (since, on average,
you'll only have to walk through 50% of the items in the collection,
because it short circuits out of the loop as soon as a match is
found).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

rkc <rk*@rkcny.comwrote in
news:e2**********************************@c58g2000 hsc.googlegroups.co
m:

That would be great except your placement of On Error Resume Next
would result in nothing
ever being added to the collection.

See my post correcting the code.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Marshall Barton]

David W. Fenton wrote:

>"David W. Fenton" <XX*******@dfenton.com.invalidwrote

>It needs an error handler to trap whatever for whatever specific
error(s) you are intending to ignore. I don't know the error
number for attempting to remove an item from a collection when the
key doens't exist, but I assume that's what you want to ignore.

So, if the error were 8000 it would be this:

Public Sub AddCriteria(criteria As ICriteria, key As Variant)
On Error GoTo errHandler

colCriteria.Remove key
colCriteria.Add criteria, key

exitRoutine:
Exit Sub

errHandler:
Select Case Err.Number
Case 8000
On Error Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description, vbExclamation,
_
"Error in AddCriteria()"
Resume exitRoutine
End Select
End Sub

I miscoded that in a rather dangerous way -- the error handler
should be:

errHandler:
Select Case Err.Number
Case 8000
Resume Next <==This is the changed line
Case Else
MsgBox Err.Number & ": " & Err.Description, vbExclamation, _
"Error in AddCriteria()"
Resume exitRoutine
End Select

But since posting, I've rethought this and have two better
alternatives. The first uses a GoTo:

Public Sub AddCriteria(criteria As ICriteria, key As Variant)
On Error GoTo errHandler

colCriteria.Remove key
errSkip:
colCriteria.Add criteria, key

exitRoutine:
Exit Sub

errHandler:
Select Case Err.Number
Case 8000
GoTo errSkip
Case Else
MsgBox Err.Number & ": " & Err.Description, vbExclamation, _
"Error in AddCriteria()"
Resume exitRoutine
End Select
End Sub

But I know people just hate GoTos, so another alternative would be
to do it differently, entirely, and that would be to just Add the
new item to the collection, and trap for the error of that key
already existing, Let's say the error number is 8001:

Public Sub AddCriteria(criteria As ICriteria, key As Variant)
On Error GoTo errHandler

errRetry:
colCriteria.Add criteria, key

exitRoutine:
Exit Sub

errHandler:
Select Case Err.Number
Case 8001
colCriteria.Remove key
GoTo errRetry
Case Else
MsgBox Err.Number & ": " & Err.Description, vbExclamation, _
"Error in AddCriteria()"
Resume exitRoutine
End Select
End Sub

But both of these actually violate one of my main principles --
don't raise an error that you've anticipated. Instead, check for
whether the error will be produced and then avoid doing something
that would create the error. Thus, you would do this:

Private Function bolKeyExists(mcolCollection As Collection, _
varValue As Variant) As Boolean
Dim varItem As Variant
Dim i As Integer
Dim ysnKeyExists As Boolean

For i = 1 To mcolCollection.Count
ysnKeyExists = (mcolCollection(i) = varValue)
If ysnKeyExits Then Exit For
Next i
bolKeyExists = ysnKeyExists
End Function

Then you'd use that function to test whether it needed to be added:

Public Sub AddCriteria(Criteria As ICriteria, varKey As Variant)
On Error GoTo errHandler

If Not (bolKeyExists(colCriteria, varKey) Then
colCriteria.Add Criteria, varKey
End If

exitRoutine:
Exit Sub

errHandler:
MsgBox Err.Number & ": " & Err.Description, vbExclamation, _
"Error in AddCriteria()"
Resume exitRoutine
End Sub

The point here is that you avoid raising an error. My philosophy is
that error handlers are expensive in terms of processing power, and
it's better to avoid raising an error that you have to trap for.
Now, the question is how expensive it is to walk a collection (as is
done in the bolKeyExists() function). This will depend, of course,
on how big the collection is, but I'm pretty sure it would take a
collection with thousands of items for a walk through the collection
to be slower than tripping the error handler (since, on average,
you'll only have to walk through 50% of the items in the collection,
because it short circuits out of the loop as soon as a match is
found).

I like your philosophy, David.

Shouldn't your GoTo errSkip
be a Resume errSkip?

--
Marsh

[lyle fairfield]

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in
news:Xn**********************************@74.209.1 36.81:

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in
news:Xn**********************************@74.209.1 36.93:

>It needs an error handler to trap whatever for whatever specific
error(s) you are intending to ignore. I don't know the error
number for attempting to remove an item from a collection when the
key doens't exist, but I assume that's what you want to ignore.

So, if the error were 8000 it would be this:

Public Sub AddCriteria(criteria As ICriteria, key As Variant)
On Error GoTo errHandler

colCriteria.Remove key
colCriteria.Add criteria, key

exitRoutine:
Exit Sub

errHandler:
Select Case Err.Number
Case 8000
On Error Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description, vbExclamation,
_
"Error in AddCriteria()"
Resume exitRoutine
End Select
End Sub

I miscoded that in a rather dangerous way -- the error handler
should be:

errHandler:
Select Case Err.Number
Case 8000
Resume Next <==This is the changed line
Case Else
MsgBox Err.Number & ": " & Err.Description, vbExclamation, _
"Error in AddCriteria()"
Resume exitRoutine
End Select

But since posting, I've rethought this and have two better
alternatives. The first uses a GoTo:

Public Sub AddCriteria(criteria As ICriteria, key As Variant)
On Error GoTo errHandler

colCriteria.Remove key
errSkip:
colCriteria.Add criteria, key

exitRoutine:
Exit Sub

errHandler:
Select Case Err.Number
Case 8000
GoTo errSkip
Case Else
MsgBox Err.Number & ": " & Err.Description, vbExclamation, _
"Error in AddCriteria()"
Resume exitRoutine
End Select
End Sub

But I know people just hate GoTos, so another alternative would be
to do it differently, entirely, and that would be to just Add the
new item to the collection, and trap for the error of that key
already existing, Let's say the error number is 8001:

Public Sub AddCriteria(criteria As ICriteria, key As Variant)
On Error GoTo errHandler

errRetry:
colCriteria.Add criteria, key

exitRoutine:
Exit Sub

errHandler:
Select Case Err.Number
Case 8001
colCriteria.Remove key
GoTo errRetry
Case Else
MsgBox Err.Number & ": " & Err.Description, vbExclamation, _
"Error in AddCriteria()"
Resume exitRoutine
End Select
End Sub

But both of these actually violate one of my main principles --
don't raise an error that you've anticipated. Instead, check for
whether the error will be produced and then avoid doing something
that would create the error. Thus, you would do this:

Private Function bolKeyExists(mcolCollection As Collection, _
varValue As Variant) As Boolean
Dim varItem As Variant
Dim i As Integer
Dim ysnKeyExists As Boolean

For i = 1 To mcolCollection.Count
ysnKeyExists = (mcolCollection(i) = varValue)
If ysnKeyExits Then Exit For
Next i
bolKeyExists = ysnKeyExists
End Function

Then you'd use that function to test whether it needed to be added:

Public Sub AddCriteria(Criteria As ICriteria, varKey As Variant)
On Error GoTo errHandler

If Not (bolKeyExists(colCriteria, varKey) Then
colCriteria.Add Criteria, varKey
End If

exitRoutine:
Exit Sub

errHandler:
MsgBox Err.Number & ": " & Err.Description, vbExclamation, _
"Error in AddCriteria()"
Resume exitRoutine
End Sub

The point here is that you avoid raising an error. My philosophy is
that error handlers are expensive in terms of processing power, and
it's better to avoid raising an error that you have to trap for.
Now, the question is how expensive it is to walk a collection (as is
done in the bolKeyExists() function). This will depend, of course,
on how big the collection is, but I'm pretty sure it would take a
collection with thousands of items for a walk through the collection
to be slower than tripping the error handler (since, on average,
you'll only have to walk through 50% of the items in the collection,
because it short circuits out of the loop as soon as a match is
found).

Thanks. This is a revelation to me. After many years I believe that I now
understand why you seldom, (almost never), post code.

--
-
lyle fairfield

[rkc]

On Sep 11, 5:06*pm, "David W. Fenton" <[e-mail]>
wrote:

"David W. Fenton" <[e-mail] innews:Xn**********************************@74.209 .136.93:

It needs an error handler to trap whatever for whatever specific
error(s) you are intending to ignore. I don't know the error
number for attempting to remove an item from a collection when the
key doens't exist, but I assume that's what you want to ignore.

So, if the error were 8000 it would be this:

*Public Sub AddCriteria(criteria As ICriteria, key As Variant)
*On Error GoTo errHandler

* *colCriteria.Remove key
* *colCriteria.Add criteria, key

*exitRoutine:
* *Exit Sub

*errHandler:
* *Select Case Err.Number
* * *Case 8000
* * * *On Error Resume Next
* * *Case Else
* * * *MsgBox Err.Number & ": " & Err.Description, vbExclamation,
* * * *_
* * * * "Error in AddCriteria()"
* * * *Resume exitRoutine
* *End Select * *
*End Sub

I miscoded that in a rather dangerous way -- the error handler
should be:

*errHandler:
* *Select Case Err.Number
* * *Case 8000
* * * *Resume Next <==This is the changed line
* * *Case Else
* * * *MsgBox Err.Number & ": " & Err.Description, vbExclamation,_
* * * * "Error in AddCriteria()"
* * * *Resume exitRoutine
* *End Select * *

But since posting, I've rethought this and have two better
alternatives. The first uses a GoTo:

*Public Sub AddCriteria(criteria As ICriteria, key As Variant)
*On Error GoTo errHandler

* *colCriteria.Remove key
*errSkip:
* *colCriteria.Add criteria, key

*exitRoutine:
* *Exit Sub

*errHandler:
* *Select Case Err.Number
* * *Case 8000
* * * *GoTo errSkip
* * *Case Else
* * * *MsgBox Err.Number & ": " & Err.Description, vbExclamation,_
* * * * "Error in AddCriteria()"
* * * *Resume exitRoutine
* *End Select * *
*End Sub

But I know people just hate GoTos, so another alternative would be
to do it differently, entirely, and that would be to just Add the
new item to the collection, and trap for the error of that key
already existing, Let's say the error number is 8001:

*Public Sub AddCriteria(criteria As ICriteria, key As Variant)
*On Error GoTo errHandler

*errRetry:
* *colCriteria.Add criteria, key

*exitRoutine:
* *Exit Sub

*errHandler:
* *Select Case Err.Number
* * *Case 8001
* * * *colCriteria.Remove key
* * * *GoTo errRetry
* * *Case Else
* * * *MsgBox Err.Number & ": " & Err.Description, vbExclamation,_
* * * * "Error in AddCriteria()"
* * * *Resume exitRoutine
* *End Select * *
*End Sub

But both of these actually violate one of my main principles --
don't raise an error that you've anticipated. Instead, check for
whether the error will be produced and then avoid doing something
that would create the error. Thus, you would do this:

Private Function bolKeyExists(mcolCollection As Collection, _
* * *varValue As Variant) As Boolean
* Dim varItem As Variant
* Dim i As Integer
* Dim ysnKeyExists As Boolean

* For i = 1 To mcolCollection.Count
* * ysnKeyExists = (mcolCollection(i) = varValue)
* * If ysnKeyExits Then Exit For
* Next i
* bolKeyExists = ysnKeyExists
End Function

Then you'd use that function to test whether it needed to be added:

*Public Sub AddCriteria(Criteria As ICriteria, varKey As Variant)
*On Error GoTo errHandler

* *If Not (bolKeyExists(colCriteria, varKey) Then
* * * colCriteria.Add Criteria, varKey
* *End If

*exitRoutine:
* *Exit Sub

*errHandler:
* *MsgBox Err.Number & ": " & Err.Description, vbExclamation, _
* * * "Error in AddCriteria()"
* *Resume exitRoutine
*End Sub

The point here is that you avoid raising an error. My philosophy is
that error handlers are expensive in terms of processing power, and
it's better to avoid raising an error that you have to trap for.
Now, the question is how expensive it is to walk a collection (as is
done in the bolKeyExists() function). This will depend, of course,
on how big the collection is, but I'm pretty sure it would take a
collection with thousands of items for a walk through the collection
to be slower than tripping the error handler (since, on average,
you'll only have to walk through 50% of the items in the collection,
because it short circuits out of the loop as soon as a match is
found).

Your bolKeyExists function does not check for a key in the collection
it checks
for a value in a collection. There is no way to check for a key in a
collection without
the possibility of raising an error.

The purpose of the function I posted was to replace an item in a
collection
that has a specific key with another item and reassigning the key to
the new
item.

[David W. Fenton]

Marshall Barton <ma*********@wowway.comwrote in
news:f1********************************@4ax.com:

Shouldn't your GoTo errSkip
be a Resume errSkip?

Yes, you're right.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

rkc <rk*@rkcny.comwrote in
news:d9**********************************@s50g2000 hsb.googlegroups.co
m:

Your bolKeyExists function does not check for a key in the
collection it checks
for a value in a collection. There is no way to check for a key in
a collection without
the possibility of raising an error.

The purpose of the function I posted was to replace an item in a
collection
that has a specific key with another item and reassigning the key
to the new
item.

Funny, but the context in which I pulled the code from is a
collection that stores PKs for a history of accessed records, and it
works just fine.

I expect that if I couldn't do what you want to do without raising
an error, I'd find some other way to get the job done that uses some
different method. I just think allowing errors that you can
anticipate to occur is bad coding.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/