Record Locking question

[Devon]

Is row-level locking possible with Access '97
and DAO ?

Everything I've read so far says the best I can
get is page-level locking. This isn't what I want.

[Jaguar]

No, you can't get row-level locking. That is only available in Client/Server apps like SQL Server and Oracle, etc. (only certain versions of them, of course).

This has been one of the most difficult to satisfy discussions. Many different schemes have been implemented to try and make it work, but the scheme that you like may be better than someone elses liking (even though they are the same result).

For example, as stated above, you could use Pessimistic locking which locks the "page" (2K worth; and depending on the length of your rows - based on the total stored bytes of the field types, you could have one or more records on each page locked).

Note: At this point I need to make a point, Access and older versions of SQL Server (I'm not going to get into which ones, you guys can look it up) allow only a certain amount of bytes in length per RECORD. Access is only 2000 bytes per record. You CAN'T put more data in a record than that. (Not including Memo's and OLE's). But it's enough to say that (most) databases out there only allow a certain lenght of record storage you will have to check the DOCs to find out what it is. For example: I had a friend who made a table that was too long (again, by counting up all bytes
of data that a completely stored record would have) and he would get the imfamous Record Too Large error when saving data. When you try to save more data bytes than the record can store in the database you WILL crash. Believe me. Completely full, you CANNOT exceed 2K. For this reason, good database design is needed.

OK, back to the discussion. You could use Optimistic locking, but you have to program into your code the ability to fix the editing issues and evaluate the cached information. What I am saying is this. When two users get a record and they both begin to edit the record, it is NOT locked yet. When one of the users save the information, the engine will immediately place a lock on the record for the update. Then it's immediately unlocked.

When the next person wants to save the information, it then has a conflict, the data that he pulled is not the same as what currently exists in the database. This is where you would need to right some code to examine the new record and the cached information on that users machine and determine (by the users input) what information is most up-to-date at that point.

This can hopefully give you the information you need to make an educated desicion.

Well, I know it's rather lengthy, but ... Another alternative is to put a field in your table (strategically placed) that you can place a user name and date time into it and that user is then editing the data and no one else can view/edit the record (whichever you decide).

It gets rather complicated with this kind of question and you need to be really creative in how you implement it.

Using optimistic locking and managing the information is what I like the best.

[Devon]

Is there any way to detect if another user
has locked a record ?

Right now I'm using a global recordset (DAO)
and I do a SELECT statement with it.
As soon as it opens I throw the
recordset into EDIT mode or ADD mode. (dbPessimistic Locking on the openresultset lockedit options)

Now User B running the same app on another machine
tries to read the first user's record but gets an error message (which is good) but it takes User B 12 seconds
to receive the message. During this time the application on his/her machine freezes (understandable).

Could I detect the record lock so User B can take less than 12 seconds to be informed of what's going on ?

[Clunietp]

To get the info quicker than 12 seconds, you'll have to do what Jaguar said:

"Well, I know it's rather lengthy, but ... Another alternative is to put a field in your table (strategically placed) that you can place a user name and date time into it and that user is then editing the data and no one else can view/edit the record (whichever you decide). "

and implement your own logic to determine whether or not you will let User B edit the record.

Also, Access 2000 with the ADO/Jet 4.0 provider uses record level locking, not page level locking

HTH

Tom