Articles   Members Online: 3
-Article/Tip Search
-News Group Search over 21 Million news group articles.
Member Area
-Account Center
-Top 10 NEW!!
-Submit Article/Tip
-Forums Upgraded!!
-My Articles
-Edit Information
-Become a Member
-Why sign up!
-Chat Online!
-Indexes NEW!!
-Build your resume
-Find a job
-Post a job
-Resume Search
-Link to us
Visit Embarcadero
Embarcadero Community
Achieve Record locking with Access 2000 Turn on/off line numbers in source code. Switch to Orginial background IDE or DSP color Comment or reply to this aritlce/tip for discussion. Bookmark this article to my favorite article(s). Print this article
Database Others
Delphi 3.x
User Rating
No Votes
# Votes
DSP, Administrator
Reference URL:
			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.


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

Vote: How useful do you find this Article/Tip?
Bad Excellent
1 2 3 4 5 6 7 8 9 10


Share this page
Download from Google

Copyright © Mendozi Enterprises LLC