Results 1 to 4 of 4
  1. #1
    Join Date
    May 2013
    Posts
    2

    Red face Unanswered: Access 2003 Multi-user problem that shouldn't happen

    I have a strange multi-user problem.
    Back-end database is access 2003 mdb file on fileshare.
    Several front-end access 2003 databases attach to the linked tables.
    Several users run their own copy of the front-end, and they use an unbound form.
    When they have entered data into the form, some code in a module I have written opens a recordset to a table, uses the Addnew to create a new record, fills in the fields and then Updates the new record.
    Everything looks fine, except several times a day, one field in this table has data corrupted.
    What seems to be happening is that if two users add records to the table at the same time, both get written correctly, then one field in one of the records gets overwritten and is changed to a different number. Sometimes it is emptied and null is written.
    How can this happen?

    Any help gratefully accepted.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    What's the key on the table that's being corrupted?

    I'm no network expert, but it occurs to me that if two users try to insert a value into the same record at more or less the same time, their individual recordsets would have the same starting conditions for incrementing an AutoNumber field. Then, when they try to commit the new record, Jet tries to write two records to the field with the same key, which would lead to a headache in most databases.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    May 2013
    Posts
    2

    Yes, but...

    Yes that makes sense.
    But the corruption is very specific.
    Both records are written.
    Almost everything is written correctly.
    But one specific numeric field in one of the records is corrupted. i.e changed to a different value.

    You are correct there is an autonumber primary key in the table.
    Other tables that are also written to at the same time also have autonumber keys and don't have a problem.

    Also, I think I have set it to pessimistic locking.

    And surely this is something that can be avoided. What would be the point of having a linked table used my multiple users, where you can't insert new records in case someone else does it at the same time.

    I've written dozens of databases over the years that do that very thing. I just assumed that access would trap this kind of thing and say something like: another user has updated the record.

    Why/how would it just let the records be added, but then corrupt one specific field in one of the new rows. With no error message. It doesn't add up.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Sorry; that was my best guess.

    All I can think of to try to fix it is to amend the problem table so that it includes a field for the current username, and then modify the primary key to include this field. Change the AutoNumber to a Long, too. You can populate historic records with dummy values. Then pick up the current user from an API function, and include it in new records. You will also need a function to pick up the largest current value for the numeric component of the key, and increment this.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

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
  •