Strange behavior in Access Queries

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/13016-strange-behavior-in-access-queries.html
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,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13016
encouragement? Help!

Thanks!

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

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.

--
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.

"Matt" wrote in message
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13016
news:45A69A30-A6DA-419C-B338-13F6F3268E88@microsoft.com...
> I've recently come accross some strange behavior in an access database
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13016
> 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!