jwcolby
jwcolby at colbyconsulting.com
Wed May 28 19:58:34 CDT 2008
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 >