Jennifer Gross
jengross at gte.net
Thu May 29 11:43:11 CDT 2008
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