Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Strange behavior in Access Queries

Discussion in 'Information Technology' started by Matt, Jul 28, 2006.

  1. Matt

    Matt
    Expand Collapse
    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!
     
  2. Loading...

    Similar Threads Forum Date
    SciTech Strange Blue World: Alien Planet's True Color Revealed, a First Breaking News Jul 12, 2013
    Truth Stranger Than Fiction! Interfaith Dialogues May 26, 2012
    Dr. Strangestlove (Or How I learned To Stop Worrying And Love My Computer) Inspirational Stories May 22, 2011
    Estranged Women Seek Help from Ramoowalia! Hard Talk Mar 9, 2011
    Nature Stranger Than Paradise (The Adivassi and FRA) Breaking News Jun 16, 2010

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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" <Matt@discussions.microsoft.com> wrote in message
    news:45A69A30-A6DA-419C-B338-13F6F3268E88@microsoft.com...
    > 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!
     

Share This Page