Results 1 to 6 of 6

Thread: I lost my key?

  1. #1
    Join Date
    Oct 2003
    Posts
    81

    Question Unanswered: I lost my key?

    I've got a 15000 + record database, 100+ tables, a whole heap of forms, reports and queries to boot.

    Yesterday I got a corrupted record. Looked it up in my table and the whole line was replaced with "#error". Not so bad I thought, I'll just delete the record. Nope. OK, so I'll compact and repair. That fixed it... or so I thought. The error was gone, but so was my primary key for that table!!!

    That's a nasty one since it took out half of my data entry capabilities for half the morning until I worked out that my key was missing.

    Can anyone shed some light on this? How can I prevent it from happening again?

    Thanks in advance

  2. #2
    Join Date
    May 2004
    Posts
    19
    Make daily backups,
    compact and repair the database more often,
    brake it to more than one database,
    upgrade to SQL server.

    And don't forget s..t happens!

    P.S. Your database is not big and it looks like it's a multi-user database. If it is it could have been a deadlock -you can't avoid it-. You can also monitor the frequency of this event.

  3. #3
    Join Date
    Oct 2003
    Posts
    81
    The problem seems to be getting worse. It corrupted by itself four times yesterday. Each time I either had to go into the data file, compact and repair, only to find that in another 15 minutes, the stupid thing did it again. This time so bad that I went into the file, compact and repair and wham.... I get booted out. Not allowing me to compact and repair.

    I was fortunate to have a backup taken about 15 minutes before, so no major loss, but that could have been a lot worse!

    What are the more typical reasons for corruptions? It hasn't been this bad before, but since I added a new section it just gets worse.

    Any help will be greatly appreciated.

  4. #4
    Join Date
    Feb 2004
    Posts
    137
    You might also make certain that all your users have the most recent update to Microsoft Jet. Earlier versions can cause frequent corruption in Multi-User environments.

  5. #5
    Join Date
    Oct 2003
    Posts
    81
    Well I have half running on Terminal Services and the other half on standalone PC's. About 35 users all up. I'm reasonably certain they all have jet 4.0 SP8, but will check that to make doubly sure.

    I've heard that autonumber keys and multiuser tables don't mix? Is this fact or fiction? If fact, how can I correct this?

    Could it be too much stress for access to handle? I'm looking at migrating to msde first and then SQL, but finding the time is the hard bit.

  6. #6
    Join Date
    Feb 2004
    Posts
    137
    Yes, there is plenty of opportunity for record-lock conflict and database curruption for Autonumber keys in a Multi-User Environment. One thing I have discovered helps is if you create a multi-field key. I have attached an example, both A97 and A2K versions.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •