Results 1 to 2 of 2
2004-01-09, 14:35 #1
- Join Date
- Jan 2004
- Long Island, New York, USA
- 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,
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?
2004-01-09, 20:53 #2
- Join Date
- Dec 2000
- Sacramento, California, USA
- 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