jwcolby
jwcolby at colbyconsulting.com
Thu May 29 11:05:49 CDT 2008
Jennifer, This is a great example of pragmatic programming. You can spend untold hours chasing that last .1% or you can give someone a button to unstick the timeclock. Thanks for that comment. John W. Colby www.ColbyConsulting.com Jennifer Gross wrote: > John, > > Though I don't do the kind of locking for unbound forms you are diving into, > I do 'lock' processes with a single username field in a process locking > table. For instance I have a time clock application where the first person > in each morning, when they go to punch the time clock, it posts yesterday's > clock and sets up today's time cards. Because people tend to arrive at the > same time I need to make sure two people don't set off this process at the > same time. So I store who's doing it and don't let anyone else into the > time clock until it is done. Every so often, perhaps once or twice a year > something goes wrong and even though the process is completed it didn't > clear out the username in the 'locked' field. Rather than have them call me > when the time clock is 'stuck' I have given one user a button on an > administrative form to 'unstick' the time clock. I bring this up because I > would encourage you to give someone at the call center the ability to > 'unlock' a record because the username did not get cleared because of > 'abnormal disconnect' or whatever other gremlins cause this kind of thing. > > Jennifer > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby > Sent: Wednesday, May 28, 2008 4:59 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] VBA Unbound data entry / update form > > Jim, > > Lots to think about, however I have no intention of taking this entire > application unbound, so a locked flag in every table is not likely. > > I didn't consider the "abnormal disconnect" thing. > > The whole point of the user name was simply to know where to go to find the > locked form and close it. In which case all that is really necessary is the > workstation name. > > Believe me, this has only ever come up in two systems in about 13 years so I > am NOT going to roll my own full blown locking system. > > John W. Colby > www.ColbyConsulting.com > > > Jim Dettman wrote: >> Couple of comments on this: >> >> 1. You do not want to use a field within a record to determine if a >> record is "busy" or not. Use a separate table. The main reason is >> that in the case of an abnormal disconnect, you are then forced to >> scan the entire table and clear that field for a user or users. >> >> There is also the issue that you would need to lock the entire table >> while placing a lock (two users go after the same record at the same >> time). This will cause major concurrency issues. >> >> 2. Any locking scheme should be centralized so it can easily be changed. >> For example, suppose rather then a simple busy/no busy flag, you want >> the ability for: >> >> A. All users being able to read a record. >> B. All users being able to read, but have only one able to update. >> C. Give one user exclusive read/write access to a record. >> >> If you've put a simple "this is the user that has this locked" field >> in every table and now need a locking level, you need to change every > table. >> With a central table, it's one change. >> >> 3. Generalize the concept of placing locks. There are many resources >> that require locking; printers, disks, records, tables, a process (ie >> an accounting close), maybe a module (keep A/R closed for all but one >> user), a company, or an entire system. >> >> To accomplish this, don't focus on record locking itself, but >> locking a resource. Typically what I do is use a "resource tag". Ie. >> For a record, it might be: >> >> <table Name>PK<primarykey> >> >> Or for an entire table simply <table name> >> >> I've also done one system where I had a resource type field. You >> could just as easily prefix a resource tag. It really doesn't matter >> what scheme you come up with, just that it's consistent. >> >> 4. Username in of itself is insufficient for locking. What if I login >> at more then one station? Or have more then one instance running on a >> specific station? Username and station name is better, but if you plan >> to allow multiple instances, you need user name, station name, and a >> window handle to come up with a unique user key. You could also use >> GUID, but would need to log the GUID's to a table along with user and >> station (aka LDB locking in JET). >> >> HTH, >> Jim. >> >> >> >> >> >> -----Original Message----- >> From: accessd-bounces at databaseadvisors.com >> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo >> Sent: Wednesday, May 28, 2008 9:01 AM >> To: 'Access Developers discussion and problem solving' >> Subject: Re: [AccessD] VBA Unbound data entry / update form >> >> I think you are misunderstand what I mean by Lock the Record. In your >> code, check if the record's new field (call it what you want), "I am >> locked By UserName" is empty or not. If it is empty, put an entry in >> it and then export the data to your working temp table. If it is not >> empty, then somebody else is working on the record and the user gets >> advised of that fact. The is no system lock involved. Only the >> logical one that you created and which is handled by your code. There >> are no decisions to make as regards what to write back. Write the lot >> back. You have the record safeguarded via your code (caveats re >> sideways access excepted). Repeat - there are no system locks >> involved in this, therefore no other unrelated records being locked. >> >> HTH >> Max >> Ps. It doesn't have to be a field that is used to indicate a "locked >> Record", you could use a global variable or write to a text file to >> say "PK is in use" and then delete that line when no longer required. >> As long as your code checks that before allow edits then it should >> work. However, there is a chance that two users may still collide >> when trying to do the "user locking routine", so you could use a temp >> table with a unique index on a field called PK. This would prevent >> two records being created for the same PK as the main table and thus >> allocated to the first user to request locking and which will prevent >> new records being added until the previous record has been destroyed >> thus preventing duplicate simultaneous attempts - holds the PK of the >> record needing to be "locked". Sorry, bit rushed that but I need to move > on. Hope it helps anyway. >> >> >> >> -----Original Message----- >> From: accessd-bounces at databaseadvisors.com >> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby >> Sent: Wednesday, May 28, 2008 12:17 PM >> To: Access Developers discussion and problem solving >> Subject: Re: [AccessD] VBA Unbound data entry / update form >> >> Max, >> >> > Won't work John. >> >> >>Make the changes. When done, look up the original record in the >> real table, compare data fields from temp to real. >> If there are any changes THEN make the appropriate decisions etc. >> This still requires a fair amount of logic to determine "old values" >> and the like... >> >> Lock fields, lock notices, timeouts etc are a "fair amount of logic" >> as well. >> >> > 2. Lock it as soon as record is accessed/edited. >> >> I can't do this. The lock affects other entirely unrelated records >> due to memo and index "page locks". THAT IS WHAT I AM TRYING TO WORK > AROUND! >> John W. Colby >> www.ColbyConsulting.com >> >> >> Max Wanadoo wrote: >>> Won't work John. >>> If you have User 1 making changes, you need to ensure that User 2 is >>> not making simultaneous changes to the same data albeit via a temp table. >>> Until you do that, nothing else makes any sense. >>> I would: >>> 1. Put a field on the table "I am locked By UserName". >>> 2. Lock it as soon as record is accessed/edited. >>> 3. Warn other users that records is being edited by UserName if they >>> try to access it. Ask them do they want to be notified when record >>> becomes available. If so, record PK and User in a temp table or in >>> variable array and check it via an OnTimer event. >>> 4. You can also put a Time-Out default if UserName left a record >>> Locked when he/she went off to luch. Time-out and lock/leave the >>> record thus unlockign it for others. >>> 5. KIS works for me. >>> Max >>> Ps. Also be aware of what you need to do if the record is locked by >>> being reported on via a Report/update batch run, etc. Remember >>> record will be incomplete. >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > >