Edited record locking problem

[polina]

Hi

I use edited record locking in my Access 97 application. But the way this lock works is not only the particular record gets locked but the whole bunch of nearby records as well.

Can anybode tell me, how many exactly records would get lock? Does it depend of the number of the records in the table or is it a fixed number

And could we actually go around that problem? Is there anyway to lock only ONE edited record?

Please advise

Thanks

 

[The_Doc_Man]

Technically, you aren't locking records. You are locking the block that the record is in. From your viewpoint, this doesn't matter UNLESS your co-worker wants the record next to yours and it happens to be in the same block.

There is no general way to prevent this behavior and still have locking within the confines of an Access soluiton. I.e., you either lock the table, the block, or nothing. There is no record-level lock in that sense.

The other part of your question: The size of the block is fixed but, given that text fields are variable length and compressed within Access, the number of records locked when their block is locked must be considered variable. You might fit one record in a block - or 50 records. I believe the block is 2K bytes, though that is an inference based on published Access statistics and limits.

Sorry, I know that didn't help you overcome your problem, but there it is for all to see.

 

[mrharvey]

"Doc" is correct, the proper terminology is called "page-locking" and in Access 97, it is most definitely in 2k chunks. However, Access 2000 DOES PROVIDE TRUE ROW (record) LOCKING!
The easy solution is to upgrade to Access 2000/Jet 4.0!

Here is a intermin work-around: if possible, re-design the table format such that, each row (record) takes up exactly 2048 bytes!
Cheesy, I know, but it is a method that might just fool Access' Mother Nature.

You must ensure the following:
1) No variable-length strings;
2) Be mindful of the storage requirements for each data-type (this info is availble via on-line help or MSDN);
3) No memo fields;
4) Double-check the tech-documentation to verify that any indexes on the TBL do not contribute to the locking page buffer (I don't think so).

Hope this helps.

Regards,
Michael