Record Locking

[TESA0_4]

Hi,
I have built an application that is split into a backend and frontend to
create a multi-user environment.

I have a series of tables that are linked in a parent child relationship
that is five levels deep. The relationship is always one to one or one to
many. The business rules require users to be able to select a record at any
level in the 'data structure' and give that record a status of 'archived' and
optionally alllow the user to request a copy of the archived record to create
a new 'draft' version. An archiving/copying request applies to the user
selected record and all 'children', 'grandchildren' etc of the record in the
data stucture.

The users have trialled the application and said the archiving/copying
function complies with their business rules.

However, I did a 'test' where I opened a second level record in one frontend
session and began editing a field so that the record was locked. I then went
to another FE session, opened the parent record and initiated the
archive/copy code. Sure enough, the parent record was archived and then
processing failed with a locked record error when the code tried to archive
the second level record.

The nature of the business is such that there is a very low probability that
two users will be working on common set of records but it is possible.

What are the options for handling this situation?

Can I use my 'tree walking' code to pre-check all records that need to be
edited to see if any are locked and, if not, 'reserve' the record pending the
actual update of the records on the tables?

Is another strategy to make a temporary copy of the records to be edited and
restore records if the update fails?

Is there a means, via code, of temporarily removing record locking so that
the archiving update will 'always' be accepted? (From a business perspective,
an Archive/copy request will always have priority over any editing another
user may be doing to a record in the data structure.)

Thanks in anticipation of some smart ideas.

Tesa

 

[dymondjack]

http://allenbrowne.com/ser-56.html

I used this in a multi-user environment, integrated with some of my own code
that checks for the active user. I keep a table of users and security values
for each user, and run a check before locking the bound controls (if Security
= 0, run the lock controls functions, if Security = 1, don't lock them).

As Allen's solution handles subforms recursively, I should think that some
fairly simple modifications to your code would allow you to programmatically
decide if certain 'users' or perhaps 'computername' variables should have
access to the subforms.

I'm not 100% sure of what you are trying to do, but I've used modifications
of this code to handle a number of similar scenarios within my db.

Hope this helps some.
--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[ntc]

I believe that one should not be able to enter directly into a 2nd level
record. And that is the issue.

A true 2nd level should only be enter-able via parent. Thus locking the
parent record too. Even if ergonomically that is unseen by the user.

But then there may be a differing use of terminology as to what you mean by
2nd level, and my university based theory days are long past... so can't be
really much help.....

 

[TESA0_4]

Thanks for your replies dymondjack and ntc. I'm still digesting them. I don't
have an IT background so I'm a bit slow on the uptake!!

In the meantime, a bit more explanation.

The 'top' table in the data structure is a register of Items, Processes and
Tasks. Each record is autonumbered (not seen by the user) and includes a
RegistrationID and Version number and description of the item, process or
task. Each record can have an 'effective status' of 'draft' which means
editable, 'Locked' which means an uneditable record for an Item, Process or
Task that is in current use or 'Archived' which means the Item, Process or
Task is retired from use or new version of the Item, Process or Task has been
created because its description has changed.

The next table is a register of Risk Assessments (of the Items, Processes &
Tasks referred to above). Each record is autonumbered (not seen by the user)
and includes a Risk AssessmentID and Version number and details of the risk
assessment. Risk assessment can have a status of Draft, Pending Acceptance,
Management Accepted, Fully Implement or Archived. A Risk Assessment can be
Archived because it is no longer relevant to the business or because a new
version has been created because a change in assessment has occured.

The next table is a register of Hazards (identified by the Risk Assessments
referred to above). Each record is autonumbered (not seen by the user) and
includes a HazardID and Version number and details of the Hazard. Like the
Risk Assessments there are various status and the ability to Archive and copy.

The next table is a register Mitigation Strategies etc etc etc

(There are some other complexities like Hazards related to Items, Processes
and Tasks that are not related to a Risk Assessment... but that doesn't
impact on my current musing on locking controls.)

Maybe this makes matters clearer for anyone else reading this thread.

 

[dymondjack]

I think I would go with the OnCurrent even of the forms, using DLookup to get
the value of your status (draft, archive, etc). I sounds like this is
something you want to do on a record-by-record basis. The table structure
makes sense, but I find that there's usually no point in validating anything
on a table level.

If you are using subforms, use Dlookup to get the status, and if your status
is draft, run LockBoundControls(Me, False). I would do this for OnCurrent on
the mainform first, and also OnCurrent for each subform (the current event
for a subform will always run after it's parent form).

Unfortunately I won't have time to dig into this any more for the next few
days, but that's probably how I would handle it. The data structures seems
straightforward enough, so if you can get that status value, and base the
lock on that, you should be ok.

hth

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[TESA0_4]

Hi dymondjack,

Really appreciate the thought and interest you have shown in my question.

Over the past 24 hours I have done more thread reading, book reading and web
searching and think that the answer to my question lies in turning the
archiving/copying process into a 'transaction' so that if an step in the
processing fails then the whoe process fails. I will need to do some
'playing' now to work out if a 'transaction' really does naswer my need and
whether I can decipher the syntax for writing the code!!

(A little more information about the application I have developed. There
isn't one big 'super form' with sub forms displaying the whole data tree I
have described. There is a Regsiter of Items, Processes & Tasks form with
subform tabs that show a summary of related Risk Assessments and Hazards.
From the sub forms a user can open a Risk Assessment form or Hazard form
which have tabbed sub forms for further layers of data etc. If an
Archving/Copying event is initiated from the Register of Items, Process &
Tasks form the processing will potential go off and modify records/append
records on every table in the data structure without any regard for form/sub
form relationships.)

Thanx,

Tesa

 

[dymondjack]

I'll start a new reply thread based on the fact that I misunderstood your
orignal question. I was under the impression that you were looking for a way
to lock certain records based on what computer the user was working from.

Can I use my 'tree walking' code to pre-check all records that need to be
edited to see if any are locked and, if not, 'reserve' the record pending the
actual update of the records on the tables?

I would assume this can be done, but I'm not sure how. However, if you do
find information on this, keep in mind that the record can always be unlocked
between the time the check would be done and your code returns to
archive/copy. A quick google search didn't show too much, so I would guess
that it might be a bit of a project to do this, if it can be done at all.

Is another strategy to make a temporary copy of the records to be edited and
restore records if the update fails?

You can always make a temp table and start the copy/archive process, and if
there is a failure, delete these records. Assuming no failures go through,
then copy from the temp table to your archive/copy tables. This is always a
good route to go, I think, quite safe and failproof once it's running. At
very least you can report to the user that a record is currently locked, and
please try again later...

Is there a means, via code, of temporarily removing record locking so that
the archiving update will 'always' be accepted?

I am not sure of any way to remove locks from records, but I'm no guru in
this particular field. I'm guessing not though...


Your best bet, IMO, would be to use the temp table method, coupled with some
error trapping. Try and set up an error trap and a timer loop to wait for a
specified amount of time, while re-trying the copy on that particular record.
If, after say 5 or 10 seconds, the record is still locked, report the error
to the user and clear the temp table. I use a similar method when dealing
with files being locked (less the temp table anyway).

You can check out the Sleep API (can be found on mvps.org I believe), or,
there are a number of threads on how to use a timer to run a loop in a
similar fashion. Ideally you would set up the loop as a wrapper function
before attempting the copy on each record.

As far as I know, it is not possible to manipulate in any way a record that
is already locked.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain