Results 1 to 8 of 8
  1. #1
    johnmerlino is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    97

    compact and repair database causes inaccuracy of data

    Hey all,

    My database reached 2 gigs fo memory. So I clicked compact and repair which shrunk it down to 200 mb. But now when I run query, it only returns 5 records, for example, when it should at least be returning 50. It was pretty close to accurate and now it is completely inaccurate. The queries didn't change. And I reimported my list of records after deleting the old list. But the inaccuracy still persists.

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    I am sorry to hear that. and we will do our best to help if you need.

  3. #3
    johnmerlino is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    97
    My queries have not changed. I looked at them and they are the same. But I just went line by line and there was a total of 44 records that it should have pulled and it didn't, even though it pulled them before I did the repair thing. Any solution to this issue? Do I need to build a new database from scratch with all new queries and import all new tables? And if so, would I have to do this everytime database reaches 2g?

    Thanks for response.

  4. #4
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    you can try to recreate the query and see what will happen.

  5. #5
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    Is the database split?

  6. #6
    johnmerlino is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    97
    The database is very basic. Only 5 tables - although one contains 700,000 records. Other than that, I have a round 12 queries.

  7. #7
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    While a C&R can cause corruption - so can reaching the 2G max.

    I've never seen a corruption result in an invalid query result. just fyi...there's a first for everything. Generally a query is always correct, perhaps a join relationship was lost or something.

    Open a brand new db. Import a table. Write a new query - and check the results.

    Hope this helps.

  8. #8
    KathyL is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Posts
    94
    Quote Originally Posted by NTC View Post
    While a C&R can cause corruption - so can reaching the 2G max.

    Open a brand new db. Import a table. Write a new query - and check the results.

    Hope this helps.
    Excellent advice here. I have had data corruption inside tables... corruption that probably sat there for months. And a query might bypass those records. So import/exporting the table to a new database and checking the results would be the first thing I would do.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Compact and repair on close
    By colotazzman in forum Access
    Replies: 2
    Last Post: 05-20-2010, 02:04 PM
  2. Automate Compact and Repair process
    By tracerbullet in forum Security
    Replies: 2
    Last Post: 01-14-2010, 05:12 PM
  3. Error 3167 Compact and Repair
    By jonsuns7 in forum Access
    Replies: 3
    Last Post: 10-27-2009, 08:19 AM
  4. Compact/Reapir doe not reduce database size
    By nosrepmodnar in forum Access
    Replies: 1
    Last Post: 10-23-2009, 02:38 AM
  5. MS-Access 2007 - Compact & Repair how?
    By techexpressinc in forum Access
    Replies: 2
    Last Post: 03-05-2009, 02:12 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums