Locking one record

[S.L.]

With following code :
Set wkDB = CurrentDb
Set wkRS = wkDB.OpenRecordset("select * from MTR1 where MTR1_CD = 'A' ",
dbOpenDynaset, dbDenyWrite)
I can open MTR1 table thru database window. Yes, I can not modify or add new
record. It locked BUT not only record that MTR1_CD = 'A'. I don't know it
lock one data page or lock the table.

Question are :
1) What does above code lock ? One data page or a whole table.
2) By programming, Can I lock only one record at a time ? How to do that ?
3) When I change from dbDenyWrite to dbDenyRead. It make me more confuse. I
can open and modify any record in MTR1. Why ? I should unable to open MTR1
table, shouldn't I ???

TIA

 

[Joel Wiseheart]

It depends on which version of Access you're using.

For Access 2000 & 2002, Click "Tools|Options|Advanced
tab|Open Databases using record level locking" option.

In Access 97, this option was not available, so it will
lock 2048 byte "pages", which can be more than one record.

To work around this problem, you can add the data size of
the existing fields, as such (you can find these listed in
the table's design view):

Field1 - long - 4 bytes
Field2 - text - 50 bytes
Field3 - long - 4 bytes
Field4 - boolean - 2 bytes
Field5 - double - 8 bytes
Field6 - text - 50 bytes
Field7 - date - 8 bytes
Field8 - date - 8 bytes
Field9 - currency - 8 bytes
Field10 - text - 50 bytes
Field11 - long - 4 bytes
Field12 - text - 50 bytes
Field13 - text - 50 bytes

For a total of 296 bytes.

You can attach 3 more text fields, max their allowed size
to 255 bytes, and fill them with some dummy character, say
255 "X"'s.

296+255+255+255=1061 bytes.

Access 97 will not lock "partial" records in a single
page. This 'tricks' the record into being over half the
size of the page (1024 bytes). This method is inefficient
and can waste a lot of disk space, but can provide
individual record locking in Access 97 if it is abosultely
critical to do so.

HTH
Joel