Re: Vanishing Joins
- From: "Chris O'C via AccessMonster.com" <u29189@uwe>
- Date: Sun, 07 Sep 2008 01:27:39 GMT
It's been so many years, I'd forgotten about this. I used to see this all
the time in dbs I inherited from a certain Access developer and the other
developers he helped - after I split their shared dbs.
You don't want to hear this because of who the guilty finger is pointing at,
but it's not a bug and it's not due to corruption. It's the consequences of
poor design and programming choices by the Access developer. The reason it's
so rare is because experienced Access developers know not to make those
choices, so they never suffer from the consequences that make you look bad in
front of your clients.
First turn off Track name AutoCorrect info. Leave it off.
Next, count how many times your app uses this line of code:
DoCmd.SetWarnings False
If you have any macros that use setwarnings no, count those too. If your
count is higher than zero, remove them from your code and your macros.
"But I can't remove them, I need them!" you say. Bull. When you make a bad
design choice you need to set warnings false to avoid the system message. So
make good design choices by exchanging every DoCmd.RunSQL and the associated
setwarnings reset for this code structure:
Dim db As Database
Set db = Currentdb
db.Execute "action query", dbFailOnError
Set db = Nothing
followed by appropriate error handling, not On Error Resume Next. "action
query" can be the name of an action query or the sql in a string.
The users won't see the "You are about to update xxx rows...Are you sure you
want to update these records?" message or the other action query messages.
They won't see any messages if the action query runs successfully. They'll
only see your error message in your error handler if something is wrong that
the user needs to know about (like the table is missing), and the action
query either won't run or it will be rolled back (if the error occurs after
Jet starts changing rows).
If you're using any macros with the setwarnings no action, convert these
macros to vba procedures and exchange those DoCmd.RunSQL commands as
described above.
I think a developer who's used Access for many years should know why Track
name AutoCorrect info should be turned off, SetWarnings should never be
turned off, DoCmd.RunSQL should never be used, and On Error Resume Next
should be avoided unless forcing the error (which is needed in maybe 1 in 200
db apps), and how these poor choices contribute to missing joins and other
unexplained changes to the db design, but if you don't, I'd be happy to
explain.
Chris
Microsoft MVP
TC wrote:
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/lost-joins-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?
--
Message posted via http://www.accessmonster.com
.
- Follow-Ups:
- Re: Vanishing Joins
- From: rumkus@xxxxxxxxxxx
- Re: Vanishing Joins
- From: Tony Toews [MVP]
- Re: Vanishing Joins
- References:
- Vanishing Joins
- From: TC
- Vanishing Joins
- Prev by Date: Re: setting control heights in a report
- Next by Date: Re: Table relationships
- Previous by thread: Re: Vanishing Joins
- Next by thread: Re: Vanishing Joins
- Index(es):
Relevant Pages
|
