Mega Code Archive
Achieve record locking with access 2000
<![CDATA[
How do I implement record locking on an Access 2000 database using Delphi?
Have you seen this on Borland Support?
Area: database\ado
Reference Number: 74076
Status: Open
Date Reported: 11/3/99
Severity: Commonly Encountered
Type: Basic Functionality Failure
Problem:
Currently, pessimistic record locking does not work
with the ADO components because ADO doesn't
provide a way to lock a record other than the
current record.
Well there is a way to lock records on MSAccess 2000 tables.
First it requires that you have the developers edition of
Microsoft Ado Data Control 6.0 (comes with Visual Studio programs).
If you have that then Import it to delphi using the Import ActiveX
menu item from the Component menu. You will see that the ActiveX has
been added as Adodc on the ActiveX palette.
Create a Form and put as many Adodc components on it as you will need
simultaneous locks. Remember this: One Adodc can lock One record in One table
at a time. So if you need to lock multiple records on multiple tables, you will
need multiple Adodc components (you have the choice of dynamic creation too).
Then create a new table in the Access MDB and name it lets say "Lock". Put two
fields in it ("lockid" type String and "fldEdt" type integer).
Below are two Functions. One called Lock, that you can use to
lock the record, or check if it is locked. The other is called Unlock
and you can use it to unlock the record.
function lock(ds:TAdoConnection;LckTable:TAdodc;const s:string;
rec,CurrentUserId:longint):boolean;
var
fnd:boolean;
s1:string;
begin
s1:=format(s,[trim(inttostr(rec))]);
LckTable.ConnectionString:=ds.ConnectionString;
LckTable.CursorLocation:=2;
LckTable.LockType:=2;
LckTable.CommandType:=2;
LckTable.RecordSource='Lock';
fnd:=false;
Try
LckTable.refresh;
if LckTable.Recordset.RecordCount>0 then
begin
LckTable.Recordset.MoveFirst;
LckTable.Recordset.Find('lockid='''+s1+'''',0,1,1);
end;
if LckTable.Recordset.RecordCount>0 then
if not (LckTable.Recordset.EOF) then
if LckTable.Recordset.Fields['lockid'].value=s1 then
fnd:=true;
if not fnd then
LckTable.Recordset.AddNew('lockid',s1);
LckTable.Recordset.Fields['fldEdt'].Value:=CurrentUserId;
result:=true;
except
result:=false;
end;
end;
function Unlock(const s:string;rec:longint;LckTable:TAdodc):boolean;
var
s1:string;
begin
s1:=format(s,[trim(inttostr(rec))]);
try
LckTable.Recordset.Cancel;
LckTable.Recordset.Find('lockid='''+s1+'''',0,1,0);
LckTable.Recordset.Delete(1);
result:=true;
except
result:=false;
end;
Now you have to do some coding inside your project.
When lets say a user requests to open a record (lets say with the unique id 12)
from your Customer table. You have an Tadodc that is called lckCustomers and is
located on the form called lckForm. Use this code:
if Lock(TCustomer.Connection,lckForm.lckCustomers,'Customers',12,1) then
begin
// the record has been succesfully locked and you can go on with your
// editing code
// ...
end
else
begin
// Ther record was allready locked by another user.
// give a message and abort the editing, or continue read only.
// ...
end;
Now if you want to unlock the record, after the editing just call:
Unlock('Customers',12,lckForm.lckCustomers);
Warning: The Lock table gets to be very large so when the first user logs in
the program, empty the lock table by using a query like 'delete from lock'.
You can check if you are the first user by checking for the existence of an ldb
file next to your mdb file. If it doesn't exist, you are the first.
That's about it. Good luck
]]>