Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Locking and lost autonumbers (2000)

    I've got a very simple database, containing approximately 10 fields, with an incrementing autonumber id.
    It's being used to catalogue video tapes of which there are around 25,000 in total.

    About 10 staff are entering the required info and there are occasional problems with locking, with an 'unable to update' message.
    This has happended a number of times but after trying again the users report that their record was saved.
    In total only 3 autonumbers have been lost. To try to avoid gaps like this in the sequence the record is saved as soon as the first text box is updated (with a non null value).

    Other info:
    1. The database is split into a front and back end
    2. Database is sitting on a server on a Novell network
    3. The front end is being run from the server (not a local copy)
    4. Essentially the database is being used for data entry, where new records are being entered pretty quickly. At present the users do not need to return to a record to edit the data.

    Can anyone offer any advice on any settings that should be changed from the defaults (in Tools>Options) to make this less likely to happen.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Locking and lost autonumbers (2000)

    About the only thing I can think of to try is to be sure the database is set to use record locking rather than page locking. That option is found under Tools / Options on the Advanced tab and says something like "Open databases using record-level locking" and should be checked. I wouldn't be concerned about having gaps in your autonumber sequence - they will always occur. In fact, because more save operations are going on than are necessary, that may be contributing to your locking problems. In any event, Access reserves the autonumber as soon as you start typing in a field for a record, so I'm dubious that there is any value in saving the record as soon as the first text box is updated.
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Locking and lost autonumbers (2000)

    AutoNumber is a convenient way to generate a unique record identifier. In general, it should be treated as an otherwise meaningless number, so that gaps in the sequence are irrelevant. If having an unbroken sequence is important to your application, an AutoNumber field may not be suitable. A gap will also be created if the user starts a new record, then cancels it by pressing Esc.

    An alternative is to use a Number (Long Integer) field, and a separate table with only one record, in which the most recently assigned value is kept. Use a dedicated unbound form in which the user can enter the data for the new record. The form should contain an OK button and a Cancel button. If the user clicks Cancel, just close the form. If the user clicks OK, open a recordset on the separate table, get the value, increment it, store it in the separate table, and then save the data, including the incremented ID value to a new record. This way you won't "lose" numbers.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locking and lost autonumbers (2000)

    Thanks, both of you.

    Hans,
    Its not really a problem if there are a few gaps and the autonumber seemed the easiest option. I thought I'd be more likely to get into locking problems using the method you describe.

    The db is using record level locking (not page) and is set to no locks.

    I suppose my question was really a more general one about locking, how to avoid it and then how to deal with it. I know 10 people isn't really a large number of users, so it worries me that a simple db is getting locked up.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Locking and lost autonumbers (2000)

    I suspect something is still amiss with your locking strategy. With Access 2000, when a user is doing data entry (adding new records), each user is supposed to get a separate new page to put records into, so as long as record locking is the default, you shouldn't be seeing locking errors. One catch is that each user's version of Access has to have that option set - it only takes one user having that option not selected to cause problems. Also, there are times I've seen Access report that error when some other error was actually occurring, but in your case it does sound like a true locking error if the user can save the record on a second attempt. You might also check and make sure that you have the latest Jet 4.0 service packs applied.
    Wendell

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Locking and lost autonumbers (2000)

    Thanks Wendell, I'll check both of those out.

    Sorry, I didn't reply before as I hadn't realised there was another reply to the 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
  •