Visual Basic&database, Locking records in Access?

Locking records in Access?

Author	Message
Tommy Nordv #1 / 7	Locking records in Access? I have a multiuser Access DB and would like to lock records when they are edited. I read the Access online help and it stated that the SQL LOCK-statement isn't supported by Access (you have to do it in Access basic). My question is thus: How can I lock records in an Access DB from VB 3.0? Any help would be greatly appreciated! -Tommy Nordvik
Mon, 06 Apr 1998 03:00:00 GMT

Robert A. Costn #2 / 7	Locking records in Access? Take a look at the CreateDynaset() command, in particular the "options". This pertains to locking. Also look at the LockEdits property if you like. -- Robert Quote: >I have a multiuser Access DB and would like to lock records when they are >edited. I read the Access online help and it stated that the SQL >LOCK-statement isn't supported by Access (you have to do it in Access basic). >My question is thus: How can I lock records in an Access DB from VB 3.0?
Mon, 06 Apr 1998 03:00:00 GMT

Frank Mers #3 / 7	Locking records in Access? Quote: >>Access 2.0 doesn't allow record locking - just 2k page locking. To lock > an >>individual record, you have to be a little creative. I use a true/false > field >>for that purpose, marking it true when a record is open of edit - and > false >>again when closed. >How does this get you round the problem of page locking ? Presumably >you still get a page lock when you update you indicator field to True >/ False ? True but, I work under the concept that if I am not actually writting to the db at the time, I don't need to have it open. Yes, I may "lock" the record as I have indicated above but, in reality, I don't have it open - I'm just preventing others from modifying it until I get finished. When I'm throught doing what I need to do with the record's data, I open the db/table, write the record back, clear my "locked" flag, and close everything again. As you can see, I don't believe in keeping a database/table/**set open any longer than I absolutely have to. This avoids a lot of write conflict problems (due to the "page" locking in Access), and greatly reduces the chances of database corruption due to a position dying with the database open in write mode. Any speed hit that is taken working this way is minimal. I don't find the loss of "bound" controls in a multi-user environment to be any great loss at all. Frank....
Fri, 10 Apr 1998 03:00:00 GMT

Frank Mers #4 / 7	Locking records in Access? Quote: >Instead of a flag, we use two fields; 1) an ID of the person who has the > record >and 2) a date/time stamp. Instead of a 'record is locked' message, you > can tell >the user who has it. You can also check to see if the user is the person > who >locked the record (they had machine problems and shut down with updating > the >record lock). We use the date/time in a similar manner, if it is over a > set >period of time we give the user the option to pull the record (we assume > that the >original record user has had problems). This seems a bit extreme, but it > is >suprising how many users exit Windows by turning their PCs off. Your user field seems to serve the same purpose as my "locked" field and, actually, I have started using the same concept. Good for checking what you left open after a crash. I like your timestamp idea for, like you say, releasing those records for the users that just went home. May have to take a hard look at that one. Frank....
Sun, 12 Apr 1998 03:00:00 GMT

Ted Lett #5 / 7	Locking records in Access? Quote: >>Instead of a flag, we use two fields; 1) an ID of the person who has the >> record >>and 2) a date/time stamp. Instead of a 'record is locked' message, you >> can tell >>the user who has it. You can also check to see if the user is the person >> who >>locked the record (they had machine problems and shut down with updating >> the >>record lock). We use the date/time in a similar manner, if it is over a >> set >>period of time we give the user the option to pull the record (we assume >> that the >>original record user has had problems). This seems a bit extreme, but it >> is >>suprising how many users exit Windows by turning their PCs off. >Your user field seems to serve the same purpose as my "locked" field and, >actually, I have started using the same concept. Good for checking what you >left open after a crash. I like your timestamp idea for, like you say, >releasing those records for the users that just went home. May have to take a >hard look at that one. >Frank.... Frank, If you are taking a look, I left off the end of our process... When a user retrieves the record, I set a timer. An hour later (if the user hasn't saved, or released the record on their own) I ask them if they wish to keep the record via a message box. If they say yes, I update the timestamp. If they say no, the record is released. If they do not respond to the prompt in 15 minutes, the record is released. How's that for user-unfriendly?
Mon, 13 Apr 1998 03:00:00 GMT

Ross Keatin #6 / 7	Locking records in Access? Quote: >><snip> >>>Access 2.0 doesn't allow record locking - just 2k page locking. To lock an >>>individual record, you have to be a little creative. I use a true/false field >>>for that purpose, marking it true when a record is open of edit - and false >>>again when closed. If it is a true/false flag in the record itself, you would need an indivisible 'test and set' operation to use it reliably. Quote: >>How does this get you round the problem of page locking ? Presumably >>you still get a page lock when you update you indicator field to True >>/ False ? >A page is still locked, but you can use LockEdits to set the page locking to only >occur on the Update not on the retrieve (i.e., it will only be locked >momentarily). I have never had a clash (that I know of) using this technique. >Instead of a flag, we use two fields; 1) an ID of the person who has the record >and 2) a date/time stamp. Instead of a 'record is locked' message, you can tell >the user who has it. You can also check to see if the user is the person who >locked the record (they had machine problems and shut down with updating the >record lock). We use the date/time in a similar manner, if it is over a set >period of time we give the user the option to pull the record (we assume that the >original record user has had problems). This seems a bit extreme, but it is >suprising how many users exit Windows by turning their PCs off. Of course if you do have a clash with Update, you can always trap the error and retry. Again, are these two fields in the record itself? If they are then there is a potential problem if you don't have an indivisible operation to test it and then set it. You would need to lock the page while testing and setting. I use a similar scheme for an order entry program when we don't want more than one person editing an order. I use another table called 'Orders in Use' which is a list of order numbers, dates and times and user names. To edit an order, I lock the entire 'Orders in Use' table, test whether the order I want is being edited or not and either display a message if it is or add it to the table. I give the users' the option to override this if they are sure someone else's machine has crashed or been switched off. Cheers. Ross
Tue, 14 Apr 1998 03:00:00 GMT

Joe Notarian #7 / 7	Locking records in Access? Quote: >Subject: Locking records in Access? >Date: 19 Oct 1995 10:29:19 GMT >I have a multiuser Access DB and would like to lock records when they are >edited. I read the Access online help and it stated that the SQL >LOCK-statement isn't supported by Access (you have to do it in Access basic). >My question is thus: How can I lock records in an Access DB from VB 3.0? Record Locking is a function of the engine only, and the client has very little control over it. The LockEdits property allows the client to specify whether a lock is placed on the data page at the time the Edit/AddNew method is invoked or left until the Update method. You must capture the error(s) to determine if locking was successful. Also remember that data is locked in 2 kb pages on the access and MS Sql engines, therefore even though two clients may be accessing separate records, if the records happen to reside on the same page, both will be subject to a lock. If you are reasonably sure that users will not be editting the same record (ie. data-entry) then set the LockEdits property of the recordset to indicate that a lock should not be placed when the Edit/AddNew method is invoked, but left until the Update method. This will improve throughput significantly.
Fri, 01 May 1998 03:00:00 GMT

Page 1 of 1

[ 7 post ]