I have read an awful lot about the record locking stratagies. What i would like is for a user to be told they cannot access a record because it is already locked by someone else. NOT a confusing message about clipboards, dropping changes or overwriting which nobody understands and which appears after they have filled in a record! I have tried all the different combinations of record locking, edited record, page locking etc but can not get this behaviour. Is it possible?
dont allow access to a record by record locking
- Thread starter Jhitch
- Start date
Depending on the PC CPU, harddrive spped, network activity, etc. and the size of the subject record, hitting the return key tryng to update the same record on two computers simultaneously may still miss the record locking because the record is updated so fast.
Utilizing the Transmit/Commit combination may catch the locking.
Utilizing the Transmit/Commit combination may catch the locking.
With record level locking in A2K and later, I think that you are worrying about a very, very rare occurrence unless you have a huge number of users simultaneously updating the same record, in which event your design startegy should be rethought.
I suggest that you trap the locking error on failed update attempts, wait 250 milliseconds and retry. Another user may be waiting too and change the record again before you can, hence futher waiting. BUt then, do you really want to update. The answer isn't necessarily simple.
7787 is the write conflict error indicating the record has be changed by another since you started updating.
7878 is the data has changed error.
In short, Jet does the record locking, not Access. Detect locked records by writing to them, triggering and detecting the resultant an error.
Litwn, Getz & Gilbert's Access 2000 Developer's Handbook, Volume 2: Enterprise Edition has extensive code examples which handle write conflicts. The 2002 edition probably has something similar, I didn't look.
The message you're encountering isn't vague or ambiguous. You just have to code appropriate action even if that action is complex.
I suggest that you trap the locking error on failed update attempts, wait 250 milliseconds and retry. Another user may be waiting too and change the record again before you can, hence futher waiting. BUt then, do you really want to update. The answer isn't necessarily simple.
7787 is the write conflict error indicating the record has be changed by another since you started updating.
7878 is the data has changed error.
In short, Jet does the record locking, not Access. Detect locked records by writing to them, triggering and detecting the resultant an error.
Litwn, Getz & Gilbert's Access 2000 Developer's Handbook, Volume 2: Enterprise Edition has extensive code examples which handle write conflicts. The 2002 edition probably has something similar, I didn't look.
The message you're encountering isn't vague or ambiguous. You just have to code appropriate action even if that action is complex.
- Local time
- Today, 11:16
- Joined
- Feb 28, 2001
- Messages
- 30,565
Basically, you CANNOT prevent record conflicts from happening. What you can do is handle them when they occur so you don't lose anything.
This is equally true whether you are talking about Jet, Access, ORACLE, FOCUS, DB2, or anything else. You see, record-level conflicts are a property of your method, not a property of a particular database. I.e. it is "native to the territory."
It goes farther than that. Suppose, just for sake of argument, that you could get to the record locking information and you checked for a lock. You found that there was none. So you start your action. BUT.... if another user on another system checked 1 millisecond before or after you checked the same record, and you both started an action, SOMEONE is going to take the hit in the shorts.
In other words, you have no way of knowing who was ahead or behind you with interest in the same information. All you can ever do is trap cases of conflict. Actually, that is far easier to do - and to do right - than it would be to design a fool-proof locking method that PREVENTS lock conflicts. Of course, you would merely move the problem. Now you have to consider lock conflicts in the LOCKING data, not in the raw record. And it is an infinite progression that doesn't stop until you run out of tracking resources.
It is yet another variant on the old Latin philosophical question, Quis custodiet ipsos custodies? (Who watches the watchers?) Or in this case, who locks the lockers?
Ilkhoutx's comments are appropriate and on the mark. I concur with his suggestion. Find a developer's guide that discusses record-locking traps and see how they approach the problem.
This is equally true whether you are talking about Jet, Access, ORACLE, FOCUS, DB2, or anything else. You see, record-level conflicts are a property of your method, not a property of a particular database. I.e. it is "native to the territory."
It goes farther than that. Suppose, just for sake of argument, that you could get to the record locking information and you checked for a lock. You found that there was none. So you start your action. BUT.... if another user on another system checked 1 millisecond before or after you checked the same record, and you both started an action, SOMEONE is going to take the hit in the shorts.
In other words, you have no way of knowing who was ahead or behind you with interest in the same information. All you can ever do is trap cases of conflict. Actually, that is far easier to do - and to do right - than it would be to design a fool-proof locking method that PREVENTS lock conflicts. Of course, you would merely move the problem. Now you have to consider lock conflicts in the LOCKING data, not in the raw record. And it is an infinite progression that doesn't stop until you run out of tracking resources.
It is yet another variant on the old Latin philosophical question, Quis custodiet ipsos custodies? (Who watches the watchers?) Or in this case, who locks the lockers?
Ilkhoutx's comments are appropriate and on the mark. I concur with his suggestion. Find a developer's guide that discusses record-locking traps and see how they approach the problem.
