Record Locks?
- Thread starter scouser
- Start date
- Local time
- Today, 05:11
- Joined
- Feb 28, 2001
- Messages
- 30,499
The problem is that even optimistic locking has a problem if the person making the edit won't leave the record. Even for the newest versions of Access, an open record is locked, PERIOD. But if you have users who walk away from an open form and open data record, you have a MAJOR problem of a much more basic type - poor Operational Security.
Point #1... NEVER EVER LEAVE ANY RECORD OPEN AND WALK AWAY. Get yourself a branding iron with inch-tall letters and tell the data clerks you will BRAND them like the dumb cows they are the first time they do this.
Millimeter-tall letters on the branding iron wouldn't be so frightening. Make the letters BIG.
OK, maybe the branding iron is a little bit extreme... I'll get serious. Get your management to allow you to make a new department policy. Make this data abuse/infraction a job event. When it happens, enter a reprimand in the offender's personnel file. Then you have grounds for a policy of "three strikes in one month and you're out." They can be terminated for cause. And in today's uncertain job market, nothing rivets attention like penalties with the potential of losing you your job.
Leaving a logged-in terminal open to a data record in a way that impedes corporate data processing is just unacceptable as a "standard operational procedure." My way of saying this would be simple. "I don't care if your back teeth are floating. Save the data and close the form before you go to the bathroom. If your choice is to save your clothes or save your job, the choice should be easy. If it is not, take a hike now and save us both the trouble later." It sounds harsh but if this is important to your company, you NEED to put some teeth into the situation.
Point #2... If this update is done through an Access form on a shared DB (as opposed to ADP methods), you can implement a solution to force the issue but you have some policy decisions to make.
There is a form property called .Timer which is a LONG Integer number of milliseconds before an event fires, entry point in the class module under the form is Form_OnTimer. It is NOT an event associated with a control. It is an event of the underlying form.
Put some humongous amount of time in the timer, like 300,000 - which is 5 minutes. (I would make it 60,000 - 1 minute - myself, but that's me.) Under each of the data controls on the form, in the AfterUpdate event of that control, reload the time with your chosen constant. If one of your clerks leaves the form open and makes no changes by the time the timer event fires, take the Form_OnTimer event. Close the form. That will unlock the record. What you have to do is decide whether to do a Me.Undo before you close the form. You can also have the form update a hidden table that tells you when a form was closed by timer and who did it.
Point #3... If you have optimistic locking on this form, auto-close plus user education will help - but you still need to provide other operational security mitigation. Things like an update history or some other method that shows when a partial update has occurred so that you can either roll back or complete the transaction when it is time to make the "close it" decision in my point #2.
Point #4... Review your overall operational methods. If you have lots of users and lots of lock collision events are cropping up, you need to consider active time-segregation of events. Like, stage all updates until a certain time. Do them in bulk. Allow a policy that says that manually updated items are time-delayed in a temp table and will be posted ONLY during the time set aside for bulk updates. Transactions entered since the last update cycle must wait for the next update cycle to be posted. (Banks do this all the time.)
Point #1... NEVER EVER LEAVE ANY RECORD OPEN AND WALK AWAY. Get yourself a branding iron with inch-tall letters and tell the data clerks you will BRAND them like the dumb cows they are the first time they do this.
Millimeter-tall letters on the branding iron wouldn't be so frightening. Make the letters BIG.OK, maybe the branding iron is a little bit extreme... I'll get serious. Get your management to allow you to make a new department policy. Make this data abuse/infraction a job event. When it happens, enter a reprimand in the offender's personnel file. Then you have grounds for a policy of "three strikes in one month and you're out." They can be terminated for cause. And in today's uncertain job market, nothing rivets attention like penalties with the potential of losing you your job.
Leaving a logged-in terminal open to a data record in a way that impedes corporate data processing is just unacceptable as a "standard operational procedure." My way of saying this would be simple. "I don't care if your back teeth are floating. Save the data and close the form before you go to the bathroom. If your choice is to save your clothes or save your job, the choice should be easy. If it is not, take a hike now and save us both the trouble later." It sounds harsh but if this is important to your company, you NEED to put some teeth into the situation.
Point #2... If this update is done through an Access form on a shared DB (as opposed to ADP methods), you can implement a solution to force the issue but you have some policy decisions to make.
There is a form property called .Timer which is a LONG Integer number of milliseconds before an event fires, entry point in the class module under the form is Form_OnTimer. It is NOT an event associated with a control. It is an event of the underlying form.
Put some humongous amount of time in the timer, like 300,000 - which is 5 minutes. (I would make it 60,000 - 1 minute - myself, but that's me.) Under each of the data controls on the form, in the AfterUpdate event of that control, reload the time with your chosen constant. If one of your clerks leaves the form open and makes no changes by the time the timer event fires, take the Form_OnTimer event. Close the form. That will unlock the record. What you have to do is decide whether to do a Me.Undo before you close the form. You can also have the form update a hidden table that tells you when a form was closed by timer and who did it.
Point #3... If you have optimistic locking on this form, auto-close plus user education will help - but you still need to provide other operational security mitigation. Things like an update history or some other method that shows when a partial update has occurred so that you can either roll back or complete the transaction when it is time to make the "close it" decision in my point #2.
Point #4... Review your overall operational methods. If you have lots of users and lots of lock collision events are cropping up, you need to consider active time-segregation of events. Like, stage all updates until a certain time. Do them in bulk. Allow a policy that says that manually updated items are time-delayed in a temp table and will be posted ONLY during the time set aside for bulk updates. Transactions entered since the last update cycle must wait for the next update cycle to be posted. (Banks do this all the time.)
New to the forum
Hi All, newbie here....I've been checking out some access forums and decided to join on this one, I like some of the well thought answers to some of the questions posted here.
So I have a question, and seeing as this post is regarding record locks, I figured I would post here.
First, if I may comment on the SQL post... your friend will have a much harder time with SQL server then MS Access, that's whether he goes with MS SQL or MySQL.
On to my question. (probably a VBA question)
Is there any way to have a form read a hidden check box, that is checked after a user saves a record....(probably a SET CHECKBOX TO YES on SAVE command), so that the record is permanently locked and no further updating or editing can be done on that record ???
Purpose...I want time and date sensitive material, such as accounting info not to be able to be changed or edited after a record is save.
If there is another way to do this without using a checkbox as a indicator that the record is not to be edited, I'm open to suggestions.
Thanks guys.
Hi All, newbie here....I've been checking out some access forums and decided to join on this one, I like some of the well thought answers to some of the questions posted here.
So I have a question, and seeing as this post is regarding record locks, I figured I would post here.
First, if I may comment on the SQL post... your friend will have a much harder time with SQL server then MS Access, that's whether he goes with MS SQL or MySQL.
On to my question. (probably a VBA question)
Is there any way to have a form read a hidden check box, that is checked after a user saves a record....(probably a SET CHECKBOX TO YES on SAVE command), so that the record is permanently locked and no further updating or editing can be done on that record ???
Purpose...I want time and date sensitive material, such as accounting info not to be able to be changed or edited after a record is save.
If there is another way to do this without using a checkbox as a indicator that the record is not to be edited, I'm open to suggestions.
Thanks guys.
- Local time
- Today, 05:11
- Joined
- Feb 28, 2001
- Messages
- 30,499
You can surely have a yes/no field that is set to "Locked" for the record, but the catch is that the application must honor the lock. This means your average user must not be permitted to open the table or query containing the locking field.
As to the mechanics of what you said, I would use a button to do the save. In the Button-click routine I would make the VBA code set the flag, which I would NEVER EVER allow to be visible on the form. If the flag is YES, then don't display the button. (Play with the ENABLED and VISIBLE properties...)
This of course presumes that there is a way for the administrator do do something that the peons cannot do. Usually this means WorkGroup security implementation, 'cause that's the easiest way to handle it.
Tell Oliver that once he has one of the form_ontimer routines working, cut-and-paste methods are applicable. Save the timer routine to an external text file. Remember, as an event routine, it ALWAYS HAS THE SAME ENTRY POINT NAME. So if you use shortcuts like Me.Close to close the form, you are OK. That way, you might really have a generic form_ontimer routine with very little extra typing. Create the module text, drop it into the class module via a cut/paste, and VIOLA you have your commonly used routine.
NOTE: You cannot make this form-on-timer handler a subroutine because Me.Close won't work correctly from the context of a public module. Has to do with binding time issues.
As to the mechanics of what you said, I would use a button to do the save. In the Button-click routine I would make the VBA code set the flag, which I would NEVER EVER allow to be visible on the form. If the flag is YES, then don't display the button. (Play with the ENABLED and VISIBLE properties...)
This of course presumes that there is a way for the administrator do do something that the peons cannot do. Usually this means WorkGroup security implementation, 'cause that's the easiest way to handle it.
Tell Oliver that once he has one of the form_ontimer routines working, cut-and-paste methods are applicable. Save the timer routine to an external text file. Remember, as an event routine, it ALWAYS HAS THE SAME ENTRY POINT NAME. So if you use shortcuts like Me.Close to close the form, you are OK. That way, you might really have a generic form_ontimer routine with very little extra typing. Create the module text, drop it into the class module via a cut/paste, and VIOLA you have your commonly used routine.
NOTE: You cannot make this form-on-timer handler a subroutine because Me.Close won't work correctly from the context of a public module. Has to do with binding time issues.
Thanks for the reply Doc Man, I will experiment with that tonight.
You brought up an interesting issue, blocking users from those records entirely, that may or may not be desirable, I would not see any downside to a user being able to view a record, as long as they couldn't change or edit it, even if they were the creator of the record.
Back to my accounting example, it would be ideal if a user were to add a record to table, for example called, accounts receivable where they are adding a payment, like a check from a customer. We would want them to add a customer name, date, check #, amount ect... but once they save the record, we would not want them to go back and edit or change the data in the record.
You brought up an interesting issue, blocking users from those records entirely, that may or may not be desirable, I would not see any downside to a user being able to view a record, as long as they couldn't change or edit it, even if they were the creator of the record.
Back to my accounting example, it would be ideal if a user were to add a record to table, for example called, accounts receivable where they are adding a payment, like a check from a customer. We would want them to add a customer name, date, check #, amount ect... but once they save the record, we would not want them to go back and edit or change the data in the record.
- Local time
- Today, 05:11
- Joined
- Feb 28, 2001
- Messages
- 30,499
Once a user can do anything to a table, they can do pretty much ANYTHING to a table. Including stuff you don't want done.
You want to constrain you users to forms. One way to do this is to do a control panel that never lets them see the DB window. Another requires you to look up the ByOwner capability 'cause you can use it to protect tables from others. In theory, you make the tables untouchable by anyone except you. But then, you create forms that use the ByOwner ability to do constrained things through the forms because you also own the forms and the system allows "you" to do things. The ByOwner makes the system think "you" are running even though it is someone else.
Personally, I like the switchboard method, but there are others who swear by the ByOwner stuff. Read up on same, then you pays your money, you takes your chances. (Yeah, I know - I'm an old carnie hustler at heart some days...)
You want to constrain you users to forms. One way to do this is to do a control panel that never lets them see the DB window. Another requires you to look up the ByOwner capability 'cause you can use it to protect tables from others. In theory, you make the tables untouchable by anyone except you. But then, you create forms that use the ByOwner ability to do constrained things through the forms because you also own the forms and the system allows "you" to do things. The ByOwner makes the system think "you" are running even though it is someone else.
Personally, I like the switchboard method, but there are others who swear by the ByOwner stuff. Read up on same, then you pays your money, you takes your chances. (Yeah, I know - I'm an old carnie hustler at heart some days...)
The_Doc_Man said:
Thanks for the fast responses Doc Man, by your words I can tell you've been working on databases a long time. I am in my second year of Access Development.
Just a little bio, I've created 4 in-house systems for my current employer and I have 5 projects outside my employer as personal ventures which have been going really well, most of my work up until very recently has been very basic in design, mostly data entry systems with minor reporting requirements, but I've been working on some projects for myself to test myself on database design and implementation, so to prepare myself for larger jobs with more complicated system requirements.
My previously posted question was not in reference to any project I'm working on, just functionality.
To which I greatly appreciate your feedback.
Eddie
