Achieve Record locking with Access 2000

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.

 

Share this article!

Follow us!

Find more helpful articles: