Results 1 to 4 of 4
  1. #1
    proski
    Guest

    Updating a Database at the same time (Access 97)

    I have an Access database with about 350 records. There are about 45 users, but only expected to be a maximum of 10 concurrently. I have one main data entry form EBPMainFORM that is set to display initially when the database opens. The database (MDB file) is marked as shared. The record locking with "no locks" is currently set. I have one filter to limit each list by department. I have a couple of macros for running reports.

    Problems/Messages encountered by users:
    Sometimes the database cannot be opened because it is "locked by user "Admin on "Machine". Sorry no picture.Sometimes you cannot save records, until other person quits out.

    Why is this happening and how can I fix it?

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

    Re: Updating a Database at the same time (Access 97)

    Access 97 uses a page locking mechanism, and if your records are fairly small, several records are stored on one page. As a result if Access thinks someone has edited a record it will lock the page. The same thing happens when new records are added. Unfortunately without security active, you can't see who the user is - everyone logs in as Admin. The machine name may help, but you need to know who and where the machine is. Using a single form as you are means everyone normally has at least one record open.

    One solution is to design the form to be driven by code and open the recordset as read-only. If people want to edit they would click an edit button to switch to an editable version of the record. But that approach involves lots of code and considerable testing. Conversion to Access 2000 would reduce your problems as it uses some different techniques, but record locking is still a problem. Sorry not to be more encouraging.
    Wendell

  3. #3
    proski
    Guest

    Re: Updating a Database at the same time (Access 97)

    I found the same information on MS TechNet. And, I checked to make sure my records are 1/2 a page at the minimum (1024 bytes) and they are. So, the page-locking theory should not be the problem, really.

    I would love to update to Access 2000, but it isn't possible at this time. It would cure a lot of my woes.

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

    Re: Updating a Database at the same time (Access 97)

    If that's the case, you may be experiencing a problem with the "lock" file. In the same directory that your database exists, you will have a file named "mydb.ldb" which contains information about extended locks that Access has placed on the database. There are several Knowledge Base articles that describe how they work and the problems that can occur: Q136128, Q186304 and Q176670.

    Another possibility is that if you are using text fields they may not actually contain a full 1024+ bytes, and then Access stuffs them into one page. A piece of data that would help significantly is the Error Message Number that users get - there are at least 3 that give similar messages but are from different causes. Is your form based on a stored query, an embedded SQL string, or is it unbound and you are editing with a recordset you open using DAO?
    Wendell

Posting Permissions

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