How-to-obtain-a-list-of-users-connected-to-an-Access-database


            Title: How to obtain a list of users connected to an Access database
Question: Sometimes it is necessary to know which users are connected to a particular Access database, for example, in order to notify them about some administrative tasks. How can I use Delphi to obtain such a list?
Answer:
To my knowledge, there are two way to accomplish this task. 
In the first one, we take advantage of the .ldb file which is automatically created and updated by Access whenver a user is connected to the database or logs off. The structure of this file is documented in Ref. [1]. Essentially, it is a binary file with equally sized records containing 32-bit wide fields for workstation and user names. For non-secured databases, the user name is usually "Admin". The following procedure reads the ldb file and, depending on the parameter "ExtractUsers", copies the user or workstation names into a stringlist:
uses
 Classes, SysUtils;
 
procedure ReadLoggedUsers(const MDBFile:string; ExtractUsers:boolean; 
 List:TStrings);
type
 TChar32 = array[0..31] of char;
 LDBRecord = record
 Workstation : TChar32;
 User : TChar32;
 end;
var
 Stm : TFileStream;
 s : string;
 rec : LDBRecord;
 i : integer;
 function Char32ToString(arr:TChar32) : string;
 var
 i : integer;
 begin
 SetLength(result, 32);
 Move(arr, result[1], 32);
 result := Trim(result);
 end;
begin
 if Listnil then begin
 List.Clear;
 s := ChangeFileExt(MDBFile, '.ldb');
 if FileExists(s) then begin
 stm := TFileStream.Create(s, fmOpenRead+fmShareDenyNone);
 try
 while stm.Position stm.Read(rec, SizeOf(rec));
 if ExtractUsers 
 then s := Char32ToString(rec.User)
 else s := Char32ToString(rec.WorkStation);
 List.Add(s);
 end;
 finally
 stm.Free;
 end;
 end;
 end;
end;
The second approach can be used in particular when the database is accessed by ADO. Here, information on connected users can be obtained from the schema tables which are read by the "OpenSchema" method of the ADOConnection. This method passes the data requested to an ADODataset into fields named "COMPUTER_NAME" and "LOGIN_NAME". See [2] for more details. 
uses
 Variants, // D6 or higher only
 Classes, ADODB;
const
 JET_SCHEMA_USERROSTER = '{947bb102-5d43-11d1-bdbf-00c04fb92675}';
procedure ReadLoggedUsers(AConnection:TADOConnection; ExtractUsers:boolean; 
 List:TStrings);
var
 Dataset : TADODataset;
 s : string;
begin
 if (AConnectionnil) and (Listnil) then begin
 List.Clear;
 Dataset := TADODataset.Create(nil);
 try
 Dataset.Connection := AConnection;
 AConnection.OpenSchema(siProviderSpecific, EmptyParam, 
 JET_SCHEMA_USERROSTER, Dataset);
 while not Dataset.EoF do begin
 if ExtractUsers 
 then s := Dataset.FieldByName('LOGIN_NAME').AsString
 else s := Dataset.FieldByName('COMPUTER_NAME').AsString;
 List.Add(s);
 Dataset.Next;
 end;
 finally
 Dataset.Free;
 end;
 end;
end;
References:
[1] http://support.microsoft.com/default.aspx?scid=kb;EN-US;136128. 
[2] http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnimo01/html/o2k0135.asp
ADO Database Delphi
