Achieve Record locking with Access 2000

Problem/Question/Abstract:

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.

Answer:

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;
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.