Mega Code Archive
How to obtain a list of users connected to an Access database
<![CDATA[
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
]]>