Strange behavior in Access Queries

[Guest]

I've recently come accross some strange behavior in an access database that
has been kicking along (unmodified) just fine for a couple of years. After
digging into a nested query that held a few joins, I could not for the life
of me figure out why it was not joining correctly. It was not making joins
that it clearly should be. To test, I recreated the top level query from
scratch (even the SQL text matched exactly) and the recreated query performed
perfectly. Blew my mind... Obviously there was some corruption with at
least that query. Since the recreated query worked fine, I figured the data
was fine. I was concerned that other queries might be suffering the same
fate and I wanted to further test the issue, so I ran a Compact and Repair.
I opened up the database and went to the same troubled query, only to find
that now it performed correctly. (I can repeat this with the same results -
pre and post compact)

What bothers me is there was no corruption noted when opening the database,
and really no way to tell something was wrong until someone noticed some data
that didn't appear quite right. Essentially, I realy could have gone years
without knowing an issue existed. How can I identify this? I don't find an
acceptable solution to be simply performing a daily compact and repair.
Where is the integrety in Access (I'm an SQL dba). Was this simply a corrupt
index in access? How can I tell if there are problems without waiting for it
to come out in the data itself. I'm just seriously concerned about data
integrity now with this small database (100MB) - any ideas, suggestions,
encouragement? Help!

Thanks!

 

[Allen Browne]

Hi Matt.

A corrupted index can indeed give exactly the symptoms you describe, and
Access may be completely ignorant of the problem.

It is disconcerting to know that there may be an unidentified issue lurking
somewhere, but IME, the problem is rare. I can think of 2 occasions where I
have seen this in 13 years of using Access. Both were fixed by a
compact'n'repair as you experienced. It may be more prevelant on machines
where writes are regularly interrupted (bad NIC, bad power, bad hardware,
bad users.)

Hopefully you will get other responses to this thread also, to give you
greater breadth of others' experiences.