Author: Vassilis Perantzakis
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.
1
2 function lock(ds: TAdoConnection; LckTable: TAdodc; const s: string;
3 rec, CurrentUserId: longint): boolean;
4 var
5 fnd: boolean;
6 s1: string;
7 begin
8 s1 := format(s, [trim(inttostr(rec))]);
9 LckTable.ConnectionString := ds.ConnectionString;
10 LckTable.CursorLocation := 2;
11 LckTable.LockType := 2;
12 LckTable.CommandType := 2;
13 LckTable.RecordSource = 'Lock';
14 fnd := false;
15 try
16 LckTable.refresh;
17 if LckTable.Recordset.RecordCount > 0 then
18 begin
19 LckTable.Recordset.MoveFirst;
20 LckTable.Recordset.Find('lockid=''' + s1 + '''', 0, 1, 1);
21 end;
22 if LckTable.Recordset.RecordCount > 0 then
23 if not (LckTable.Recordset.EOF) then
24 if LckTable.Recordset.Fields['lockid'].value = s1 then
25 fnd := true;
26 if not fnd then
27 LckTable.Recordset.AddNew('lockid', s1);
28 LckTable.Recordset.Fields['fldEdt'].Value := CurrentUserId;
29 result := true;
30 except
31 result := false;
32 end;
33 end;
34
35 function Unlock(const s: string; rec: longint; LckTable: TAdodc): boolean;
36 var
37 s1: string;
38 begin
39 s1 := format(s, [trim(inttostr(rec))]);
40 try
41 LckTable.Recordset.Cancel;
42 LckTable.Recordset.Find('lockid=''' + s1 + '''', 0, 1, 0);
43 LckTable.Recordset.Delete(1);
44 result := true;
45 except
46 result := false;
47 end;
48 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:
49
50 if Lock(TCustomer.Connection, lckForm.lckCustomers, 'Customers', 12, 1) then
51 begin
52 // the record has been succesfully locked and you can go on with your
53 // editing code
54 // ...
55 end
56 else
57 begin
58 // Ther record was allready locked by another user.
59 // give a message and abort the editing, or continue read only.
60 // ...
61 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.
|
