Author: Ronald Buster 


Ever wondered how to retrieve the last insert id in MsAccess, of the autoincrement 
field from a table.

Answer:

We have a table in MsAccess like : 

Test, Fields (id=autoinc, name=text); 

First we have to have a function like the one below : 

1   function GetLastInsertID: integer;
2   begin
3   
4     // datResult = TADODataSet
5   
6     datResult.Active := False;
7     datResult.CommandText := 'select @@IDENTITY as [ID]';
8     datResult.Active := True;
9   
10    Result := datResult.FieldByName('id').AsInteger;
11  
12    datResult.Active := False;
13  
14  end;


Now before getting the last inserted record record id = autoincrement field, in 
other words calling the above function. You have to do a SQL insert like the 
following 

15  procedure InsertRec;
16  begin
17  
18    // datCommand = TADOCommand
19  
20    datCommand.CommandText := 'insert into [test] ( [name] ) values ( "Test" )';
21    datCommand.Execute;
22  
23  end;


Now if we like to know which is the last autoinc value ( notice that the 
getlastinsertid proc. only works after the insertrec proc) 

24  procedure Test;
25  begin
26    InsertRec;
27    Showmessage(format('lastinsertid : %d', [GetLastInsertID]));
28  end;


Hope you can make this work, it works for me, any questions feel free to ask