Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Location
    Long Island, New York, USA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Database Locking problem (Access 2000)

    I have a fairly complex Microsoft Access 2000 database that uses lots
    of VB Code. In the VB code, I had been creating a Database (DB) object
    and Recordset (RS - DAO) object as needed and then destroying them
    when I was finished.

    In cleaning up my code, I created a single, global DB object that
    stays memory resident, and continue to create and destroy RS objects
    as needed. All RS objects access the same DB object. I figure this
    saves processor time (creation and destruction) helps minimize bloat,
    etc.

    However, since doing this, I have been experiencing an odd problem. If
    any user causes a record locking situation, they are seeing a message
    that says, "Record locked, cannot update". Immediately following this,
    the entire database for all users connected to it gets locked and no
    one can work. I then have to kick everyone out, and do a Repair and
    Compact. Once they go back in everything is fine.

    Now, I am assuming the DB change mentioned above is the culprit
    because its implementation seems to coincide with the appearance of
    this problem. I have no other evidence to support this though.

    The advanced tab under Tools/Options is set to "No Locks" and it is
    Checked where it says "Open databases using record level locking"

    My thought here is that perhaps this global DB object is somehow
    setting the Databases locking method to ALL Records, instead of no
    locking, or record level locking, but I cannot find anyway to test
    this or to change it.

    Can anyone else provide any insight into this matter?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Database Locking problem (Access 2000)

    Do you mean you're using a VB front end, or are you talking about VBA code in an Access front end?

    The creation and instantiation of a global database object in Access shouldn't be causing the problem because the locking scheme is determine by the front end database settings. It will keep the connection to the back end open, which means there will always be an ldb file on the backend file, even when no one is actively using the data. I would investigate your handling of locking conflicts to see what might be going wrong. With record level locking, you can still get conflicts, especially if the recordsets involve multiple tables.
    Charlotte

Posting Permissions

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