I have developed an application for recording work place risk assessments and
hazards. Once a risk assessment is 'approved for use' the record needs to be
inviolate. If the risk assessment needs to be revised/edited I have a
procedure that update the superseded record with a status of 'Archived' and
creates a copy with a new version number and a status of 'Draft' that can
then be edited and processed through to 'Approved for Use'.
However, a risk assessment is not a single record on a single table. Each
risk assessment may have many hazards (on another table) and each hazard may
have many risk mitigation strategies (on another table) etc.
There is a low level possibility that one user will call for the 'archiving
and copying' of a risk assessment while another user is accessing one of the
'child' hazard or other records of the risk assessment. My concern is that a
conflict will arise that will interupt the archive/copy process midway
through execution. If this happened I can quickly 'clean-up' the situation
without directly editing the tables but that is because I understand the data
structure. Other users are likely to become thoroughly confused.
If possible, I want to avoid forcing the archive/copy process to occur only
when a user has exclusive access to the DB. Can someone offer a strategy for
managing this scenario so that I can be sure the archive and copy process
does not get interupted by a conflict with a another user?
Can I make my archive/copy procedure do a first pass through the records to
be edited/copied to check that no other user is accessing them and then
'lock' the records until the actual archiving/copying is complete? Other
strategies??
Thanks in anticipation of any suggestions!!

This sounds very similar to a post not too long ago where the op was advised
to use a single table with a status field rather than trying to copy
information between different tables.

In any case, search the help files for "Transactions". A Transaction allows
you to specifiy a set of tasks to be done, and if they aren't all done as
required, the entire transaction rolls back.

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



"TESA0_4" wrote:

> I have developed an application for recording work place risk assessments and
> hazards. Once a risk assessment is 'approved for use' the record needs to be
> inviolate. If the risk assessment needs to be revised/edited I have a
> procedure that update the superseded record with a status of 'Archived' and
> creates a copy with a new version number and a status of 'Draft' that can
> then be edited and processed through to 'Approved for Use'.
> However, a risk assessment is not a single record on a single table. Each
> risk assessment may have many hazards (on another table) and each hazard may
> have many risk mitigation strategies (on another table) etc.
> There is a low level possibility that one user will call for the 'archiving
> and copying' of a risk assessment while another user is accessing one of the
> 'child' hazard or other records of the risk assessment. My concern is that a
> conflict will arise that will interupt the archive/copy process midway
> through execution. If this happened I can quickly 'clean-up' the situation
> without directly editing the tables but that is because I understand the data
> structure. Other users are likely to become thoroughly confused.
> If possible, I want to avoid forcing the archive/copy process to occur only
> when a user has exclusive access to the DB. Can someone offer a strategy for
> managing this scenario so that I can be sure the archive and copy process
> does not get interupted by a conflict with a another user?
> Can I make my archive/copy procedure do a first pass through the records to
> be edited/copied to check that no other user is accessing them and then
> 'lock' the records until the actual archiving/copying is complete? Other
> strategies??
> Thanks in anticipation of any suggestions!!

Thanks Jack. You have put me on the right track. I was not aware of the
Begin, Commit and Rollback Transaction Statements.

"Jack Leach" wrote:

> This sounds very similar to a post not too long ago where the op was advised
> to use a single table with a status field rather than trying to copy
> information between different tables.
>
> In any case, search the help files for "Transactions". A Transaction allows
> you to specifiy a set of tasks to be done, and if they aren't all done as
> required, the entire transaction rolls back.
>
> hth
> --
> Jack Leach
> www.tristatemachine.com
>
> "I haven't failed, I've found ten thousand ways that don't work."
> -Thomas Edison (1847-1931)
>
>
>
> "TESA0_4" wrote:
>
> > I have developed an application for recording work place risk assessments and
> > hazards. Once a risk assessment is 'approved for use' the record needs to be
> > inviolate. If the risk assessment needs to be revised/edited I have a
> > procedure that update the superseded record with a status of 'Archived' and
> > creates a copy with a new version number and a status of 'Draft' that can
> > then be edited and processed through to 'Approved for Use'.
> > However, a risk assessment is not a single record on a single table. Each
> > risk assessment may have many hazards (on another table) and each hazard may
> > have many risk mitigation strategies (on another table) etc.
> > There is a low level possibility that one user will call for the 'archiving
> > and copying' of a risk assessment while another user is accessing one of the
> > 'child' hazard or other records of the risk assessment. My concern is that a
> > conflict will arise that will interupt the archive/copy process midway
> > through execution. If this happened I can quickly 'clean-up' the situation
> > without directly editing the tables but that is because I understand the data
> > structure. Other users are likely to become thoroughly confused.
> > If possible, I want to avoid forcing the archive/copy process to occur only
> > when a user has exclusive access to the DB. Can someone offer a strategy for
> > managing this scenario so that I can be sure the archive and copy process
> > does not get interupted by a conflict with a another user?
> > Can I make my archive/copy procedure do a first pass through the records to
> > be edited/copied to check that no other user is accessing them and then
> > 'lock' the records until the actual archiving/copying is complete? Other
> > strategies??
> > Thanks in anticipation of any suggestions!!