Tools for record locking

[swap m via AccessMonster.com]

Hi all,
Is there any tool to achieve reocrd locking in MS Access.
Thanks,
Swapna.

 

[Allen Browne]

Answered in m.p.a and c.d.m.a

 

[Michel Walsh]

Hi,


Locking as keeping anyone else outside, or locking as in not allowing
modifications? In the latter case, here a possible setting that logically
lock/unlock given records.

Table to be locked: table1, primary key field, pk.

Table keeping the keys of the "locked" records, locks, with a single
key, lock.


Goal: if a record in table1 has its pk value in locks.lock, the record
should not be deleted, neither modified. So, to logically lock a record, we
will write its pk value into locks.lock, and to "unlock" the record, we will
just remove its value from locks.lock.



1- Make a data referential integrity from locks into table1 such that
locks.lock must be present in table1.pk. DO NO CASCADE the DELETE, neither
cascade update, but be sure to enforce the integrity.

Doing so, it would then be impossible to delete a record in table1
that is in locks. You cannot either modify the pk value in table1 if the pk
is in locks.lock. Half of the job is done.



2- Add the following CHECK constraint in table table1:

CHECK( NOT EXISTS( SELECT * FROM locks WHERE lock=pk) )


If you modify a field in table1, for a given record, we have its pk
value. Before the modification would be accepted, the CHECK constraint will
be evaluated. It will return false if the pk value is in locks.lock, which
is ok, since we want to protect the record against modifications, in that
case.




To add a CHECK constraint, you need Jet 4.0 and to use ADO (not DAO) like,
in the Immediate Debug Window:

CurrentProject.Connection.Execute "ALTER TABLE tableName ADD CONSTRAINT
constraintName CHECK( ... )"


The constraintName must be unique in the whole database. You can drop the
constraint with

CurrentProject.Connection.Execute "ALTER TABLE tableName DROP
CONSTRAINT constraintName"


There is no User Interface to handle the CHECK constraints, even if they are
quite powerful.


Hoping it may help,
Vanderghast, Access MVP

 

[peregenem]

Add the following CHECK constraint in table table1:

CHECK( NOT EXISTS( SELECT * FROM locks WHERE lock=pk) )

I can't get this to work

CREATE TABLE table1 (
pk INTEGER NOT NULL PRIMARY KEY,
data_col VARCHAR(255) NOT NULL);

CREATE TABLE locks (
lock INTEGER NOT NULL
REFERENCES table1 (pk)
ON DELETE NO ACTION
ON UPDATE NO ACTION);

ALTER TABLE table1 ADD CONSTRAINT mycheck
CHECK( NOT EXISTS( SELECT * FROM locks WHERE lock=pk) );

I get the error, 'Syntax error in CHECK constraint clause.'

 

[Michel Walsh]

Hi,


You have to run one (1) DDL (Date Definition Language) statement at a time.
(a little bit as using GO in t-sql, if you are familiar with t-sql batches,
but JET can handle just one statement at a time, in almost all situations)

Only ADO (not DAO, not the query designer) will accept the CHECK
constraint, so you have to use the Immediate Debug Window (or VBA code):

CurrentProject.Connection.Execute "ALTER TABLE .... ADD ... CHECK( ... )
"



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


Note that once installed, the CHECK constraint *works fine under
DAO* too. When I say "only ADO", I was referring to the creation itself
(or deletion) of the CHECK constraint.


Vanderghast, Access MVP

 

[peregenem]

You have to run one (1) DDL (Date Definition Language) statement at a time.
Only ADO (not DAO, not the query designer) will accept the CHECK
constraint

I am running the DDL using ADO. The CREATE TABLE statements execute
successfully but the ALTER TABLE fails with 'Syntax error in CHECK
constraint clause.'

 

[peregenem]

Note that once installed, the CHECK constraint *works fine under
DAO* too. When I say "only ADO", I was referring to the creation itself
(or deletion) of the CHECK constraint.

I can't get ADO to create the CHECK. I did a copy and paste of your
original CHECK as posted up thread but it gives an error. Please
re-post the DLL you are using to create your CHECK constraint. Thanks.

 

[Michel Walsh]

Hi,


seems that in *this* case, fields have to be fully qualified (while I wrote
many CHECK constraint between tables, before, in Jet (that is not allowed in
MS SQL Server), and that requirement was not mandatory... but if that is
what it is required, let us do it... )


==========================
CurrentProject.Connection.Execute "CREATE TABLE table1 (pk INTEGER NOT NULL
PRIMARY KEY,data_col VARCHAR(255) NOT NULL);"

CurrentProject.Connection.Execute "CREATE TABLE locks (lock INTEGER NOT NULL
REFERENCES table1 (pk)ON DELETE NO ACTION ON UPDATE NO ACTION);"

CurrentProject.Connection.Execute "ALTER TABLE table1 ADD CONSTRAINT myCheck
CHECK(NOT EXISTS(SELECT * FROM locks WHERE table1.pk=locks.lock)); "


'CurrentProject.Connection.Execute "ALTER TABLE table1 DROP CONSTRAINT
myCheck"
===========================


Adding a pk value in locks.lock will forbid any change (update, delete) in
the corresponding record in table1, with a default error message like:


---------------------
One or more values are prohibited by the validation rule 'myCheck'
set for 'table1'. Enter a value that the expression for that field can
accept. [OK] [Help]
---------------------


Removing the pk value from locks.lock will give back the default behavior.
So, you have a way to logically lock (forbid update/delete) individual
record, dynamically.



Hoping it may help,
Vanderghast, Access MVP

 

[peregenem]

seems that in *this* case, fields have to be fully qualified

Thanks for clarifying. Nice work, BTW.