Access Record Locking Confusion

ions · Oct 6, 2006

In OPTIONS -> Advanced I have the option Open Database using Record-Level locking ticked.

What is strange is record level locking works on Form1 (Form1.recordlocks= Edited) as long as Form2 has Form2.recordlocks = No Locks. If I change the Form2.recordlocks = Edited, Form1's locking becomes page level locking.

This is even true if Form2 is not open...

Additionally, Form2's locking is always page level locking if Form2.recordlock = edited.

I would like all my forms to perform RECORD level locking not page Level locking. How do I accomplish this.

I've been trying to resolve this issue for about 2 months now but no answers from the community.

Thanks for any comments.

KeithG · Oct 6, 2006

I have been trying to resolve the same issue. I read in the Microsoft knowledge base that if the user opens the database by a shortcut or by clicking the .mdb file that the database will open with Page level locking instead of Record level locking. They say a work around for this is to open up the Microsoft Access program (not an actual database just the program) and select File/Open and then navigate to your database and open it up. What version of Access are you using?

ions · Oct 6, 2006

Hi Keith G .. I will test that out. I am using Access 2002.

gemma-the-husky · Oct 7, 2006

Even if you use record-level locking, Windows will actually lock the page(s) in which the record resides, which will probably also lock neighbouring records. Look in "locking records" in Access help. Is this happening here?

Pat Hartman · Oct 9, 2006

What version of Access are you using? What version of Jet are you using? I'm not sure that any but the latest versions support record level locking and I'm not even sure about them despite what you get for options under locks.

ions · Oct 11, 2006

Hi Pat.

I am using Access 2002. I seem to be getting Record Level Locking on some forms Pat.

Pat Hartman · Oct 12, 2006

How can you tell that you are getting record level locking? Can you determine the other records that might be in the same segment as the record that is locked?

The choices are "No Locks", "All Records", and "Edited Record". However, "Edited Record" may extend to more than one record, especially if your record length is short. In mainframe terminology, locks are at the block level which is the level at which physical I/O is performed. I think the equivalent in PC terminology is segment. A disk fetch returns a "segment" which may contain more than one logical record.

You don't say whether you are seeing too many locks or not enough. I'm guessing that you are seeing too many. That should be explained by the unit of measure at which the PC does physical I/O. Make sure that the records you are testing with would be far enough apart to be in different segments.

ions · Oct 18, 2006

Hmmm, you are right. I assumed the records were located on the same page / segment because they are stored next to each other in the table.

So basically that record level option doesn't work? Record level locking is not achievable in Access 2002?

Steve R. · Oct 18, 2006

This has been an irritating issue for me. I have a database that I am going to "convert" from a single user to a multisuser database. When testing as a multiuser, I get some locking, but it is erratic and I have not been able to pinpoint a cause. Just the other day, I received a message that I could not open the database, because another user was using it. They where, but I immediately retried and got in on the second attempt????????

Access Record Locking Confusion

ions

Access User

KeithG

AWF VIP

ions

Access User

gemma-the-husky

Super Moderator

Pat Hartman

Super Moderator

ions

Access User

Pat Hartman

Super Moderator

ions

Access User

Steve R.

Retired

Similar threads

Users who are viewing this thread