Récupération de l'identifiant auto-incrémenté du dernier enregistrement inséré dans une base de données Access

Soit une base Access, avec une table nommée PERSONNE.

La déclaration SQL de la table est la suivante :

CREATE TABLE PERSONNE (
      ID_PERSONNE COUNTER CONSTRAINT MyFieldConstraint PRIMARY KEY,
      NOM VARCHAR(100),
      PRENOM VARCHAR(50),
      EMAIL VARCHAR(200),
      AGE INT
);

ID_PERSONNE est un index auto-incrémenté : chaque fois que vous ajoutez un enregistrement à la table, sa valeur est générée automatiquement par la base.

La commande SQL suivante :

INSERT INTO
   PERSONNE(NOM,PRENOM,EMAIL,AGE)
   VALUES('DUPONT', 'Bernard', 'b.dupont@developpez.com', 35);

ajoute l'enregistrement dans la table :

II-B-1. MySQL & PHP▲

II-B-2. Interbase▲

II-B-3. SQL Server 2000▲

II-B-4. SQL Server (avant 2000)▲

II-B-5. Access▲

C'est la méthode bricolage que trouvera rapidement le débutant. L'enregistrement inséré se retrouve forcément à la fin de la table. Pour le sélectionner, il suffit alors de trier les enregistrements par ordre d'index décroissant :

SELECT * 
FROM PERSONNE 
ORDER BY ID_PERSONNE DESC;

Cette méthode est barbare, car elle suppose qu'il n'y a qu'un seul utilisateur connecté simultanément à la base. Elle ne gère pas les accès concurrents.

Prenons l'exemple d'un site marchand, deux utilisateurs créent en même temps un caddie :

instant T + 1 : l'utilisateur 1 récupère le caddie du 2^e utilisateur.

instant T + 2 : l'utilisateur 2 récupère son propre caddie.

conséquences : l'utilisateur 2 verra son caddie s'enrichir spontanément en fonction des choix de l'utilisateur 1 et réciproquement !

Cette méthode barbare est à proscrire. Le chapitre qui suit expose une méthode qui tient compte des accès concurrents.

Cette méthode est standard. Elle s'appuie sur l'utilisation de l'objet ADO recordset. Elle fonctionne convenablement, mais je pense qu'elle est susceptible de générer des surprises lors d'une montée en charge du serveur et d'écraser des index. Je la présente pour information.

1. Ouverture de la connexion ADO :

    

   Sélectionnez

   1.
   2.
   3.
   

   set objCon = Server.CreateObject("ADODB.Connection")
   objCon.ConnectionString ="base_tutorial"
   objCon.Open
   

2. Création d'un objet recordset :

    

   Sélectionnez

   1.
   2.
   3.
   

   set rs1 = Server.CreateObject("ADODB.Recordset")
    rs1.Open "SELECT * FROM PERSONNE",objCon, adOpenKetset, adLockOptimistic
    rs1.addNew
   

3. Affectation et mise à jour :

rs1("NOM")='Dupont'
rs1.update
rs1.Close

III-C-1. Explication▲

SELECT MAX(AGE) AS max
   FROM PERSONNE

INSERT INTO
   PERSONNE(NOM,PRENOM,EMAIL,AGE)
   VALUES('DUPONT', 'Bernard', 'b.dupont@developpez.com', R);

SELECT ID_PERSONNE
   FROM PERSONNE
   WHERE AGE=M;

UPDATE PERSONNE
   SET AGE=35
   WHERE ID_PERSONNE=ID;

III-C-2. Deux variantes encore plus fiables▲

CREATE TABLE SOCIETE (
      ID_SOCIETE COUNTER CONSTRAINT MyFieldConstraint PRIMARY KEY,
      . . .
      . . .
);

SELECT MAX(ID_SOCIETE) AS max
   FROM SOCIETE

SELECT ID_PERSONNE
FROM PERSONNE
WHERE NOM=N;

III-C-3. Optimisation en vitesse▲

III-D-1. La maîtrise des contraintes de son environnement▲

III-D-2. Solution ISAPI avec Delphi 6▲

var
  FLock: TCriticalSection;

initialization
   if not Assigned(fLock) then
      fLock := TCriticalSection.Create;
finalization
   fLock.Free;

try
    FLock.Enter;
{ 
  INSERTION DE L'ENREGISTREMENT
}
  finally
    FLock.Release;
  end;

Les variables suivantes contiennent les informations du nouvel enregistrement :

nom = « DUPONT »;

prenom = « Bernard »;

email = « b.dupont@developpez.com »;

age = 35;

Lors du premier INSERT, on appelle la fonction filtre :

Function filtre(s)
   filtre = replace(s,"'","''")
end Function

Son rôle est de doubler les caractères quote ' dans les lignes de commandes SQL.

1. Ouverture de la connexion ADO :

    

   Sélectionnez

   1.
   2.
   3.
   

   set objCon = Server.CreateObject("ADODB.Connection")
   objCon.ConnectionString ="base_tutorial"
   objCon.Open
   

2. Création d'un objet recordset :
   set rs1 = Server.CreateObject("ADODB.Recordset")

3. Nous récupérons la valeur maximale de AGE. Nous ne récupérons pas directement la valeur de MAX(AGE), car il n'y a pas de valeur retournée dans le cas d'une table vide. Nous prenons la précaution de vérifier si la table contient des enregistrements.

    

   Sélectionnez

   1.
   2.
   3.
   4.
   5.
   6.
   7.
   8.
   9.
   

   rs1.Open "SELECT COUNT(*) AS recordcount FROM PERSONNE", objCon
       if rs1("recordcount") = 0 then
           m = 0
       else
           rs1.Close
           rs1.Open "SELECT MAX(AGE) AS max FROM PERSONNE", objCon
           m = rs1("max")
       end if
       rs1.Close
   

   La variable m contient la valeur maximale de AGE.

4. Nous générons la valeur aléatoire r :
   r = Int(Rnd * 100000) + m

5. Nous insérons l'enregistrement, avec r à la place de AGE :

    

   Sélectionnez

   1.
   

   objCon.Execute("INSERT INTO PERSONNE(NOM,PRENOM,EMAIL,AGE) VALUES('" & filtre(nom) & "', '" & filtre(prenom) & "', '" & filtre(email) & "', " & m & ");")
   

6. Nous récupérons l'index ID_PERSONNE :

    

   Sélectionnez

   1.
   2.
   3.
   

   rs1.Open "SELECT ID_PERSONNE FROM PERSONNE WHERE AGE=" & r, objCon
   ID = rs1("ID_PERSONNE")
   rs1.Close
   

   ID contient la valeur de l'index inséré.

7. Nous mettons à jour l'enregistrement avec la bonne valeur affectée à AGE :

    

   Sélectionnez

   1.
   

   objCon.Execute("UPDATE PERSONNE SET AGE=" & age & " WHERE ID_PERSONNE=" & ID)
   

8. Fermeture de la connexion ADO.

objCon.Close
    Set objCon = nothing

Le programme suivant a été réduit au maximum afin de ne présenter que du code utile à notre démonstration.
Télécharger la source. Pour créer la base Access et la liaison DSN, reportez-vous à l'annexe Annexe.

Il contient une fiche :

Et un DataModule :

Propriétés des composants du DataModule :

1. DataSource1 : TDataSource;
   DataSet := ADOQuery1;
2. ADOConnection1 : TADOConnection;
   ConnectionString := 'base_tutorial';
   LoginPrompt := false;
   
   puis ADOConnection1.Connected := true;

Fonctionnement :

Le clic sur le bouton Insérer provoque l'ajout de l'enregistrement dans la base.

Section critique :

N'oubliez pas que vous pouvez compléter le code avec une section critique cf. III-D-2Solution ISAPI avec Delphi 6

Source :

unit UMain;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls;

type
  TFMain = class(TForm)
    Button1: TButton;
    EdNom: TEdit;
    EdPrenom: TEdit;
    EdEmail: TEdit;
    EdAge: TEdit;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    GroupBox1: TGroupBox;
    GroupBox2: TGroupBox;
    LabID: TLabel;
    procedure Button1Click(Sender: TObject);
  private
    { Déclarations privées }
    function filtre(s:string):string;
  public
    { Déclarations publiques }
  end;

var
  FMain: TFMain;

implementation
uses UDataModule1;
{$R *.dfm}

function TFMain.filtre(s:string):string;
begin
  stringreplace(s,'''','''''',[rfReplaceAll]);
end;

procedure TFMain.Button1Click(Sender: TObject);
var
  M : integer;
  SqlQuery : string;
  R,ID : cardinal;
  nom,prenom,email : string;
  age : integer;
begin
  nom := EdNom.text;
  prenom := EdPrenom.text;
  email := EdEmail.text;
  age := StrToInt(EdAge.text);

  with DataModule1.ADOQuery1 do begin
  Close;
  SQL.Clear;
  try
    SqlQuery := 'SELECT COUNT(*) AS recordcount FROM PERSONNE';
    SQL.Add(SqlQuery);
    Open;
    if FieldValues['recordcount'] = 0 then
      M := 0
    else begin
      Close;
      SQL.Clear;
      SqlQuery := 'SELECT MAX(AGE) AS max FROM PERSONNE';
      SQL.Add(SqlQuery);
      Open;
      M := FieldValues['max'];
    end;

    randomize;
    R := Random(100000) + 1 + M;
    Close;
    SQL.Clear;
    SqlQuery := 'INSERT INTO PERSONNE(NOM,PRENOM,EMAIL,AGE) '
      + 'VALUES(''' + filtre(nom) + ''', ''' + filtre(prenom) + ''', '''
      + filtre(email) + ''', ' + IntToStr(R) + ');';
    SQL.Add(SqlQuery);
    ExecSQL;

    Close;
    SQL.Clear;
    SqlQuery := 'SELECT ID_PERSONNE FROM PERSONNE WHERE AGE=' + IntToStr(R);
    SQL.Add(SqlQuery);
    Open;
    ID := FieldValues['ID_PERSONNE'];
    LabID.Caption := IntToStr(ID);

    Close;
    SQL.Clear;
    SqlQuery := 'UPDATE PERSONNE SET AGE=' + IntToStr(age)
      + ' WHERE ID_PERSONNE=' + IntToStr(ID);
    SQL.Add(SqlQuery);
    ExecSQL;

    except
      on E:Exception do
        ShowMessage(SqlQuery + #13 + E.Message);
    end;
  end;
end;

end.

Rappel : soit une base Access, avec une table nommée PERSONNE.

La déclaration SQL de la table est la suivante :

CREATE TABLE PERSONNE (
      ID_PERSONNE COUNTER CONSTRAINT MyFieldConstraint PRIMARY KEY,
      NOM VARCHAR(100),
      PRENOM VARCHAR(50),
      EMAIL VARCHAR(200),
      AGE INT
   );

Pour créer une nouvelle base, ouvrir Access, choisir la création d'une nouvelle base de données :

Il y a deux façons de créer une table. À la main, ou par une requête.

1. Créer une table à la main
   Cliquez sur le bouton Nouveau :

   Remplissez les champs de la façon suivante :
   
   ID_PERSONNE : NuméroAuto, Entier long, Incrément, indexé sans doublons
   NOM : Texte, taille 100, non indexé
   PRENOM : Texte, taille 50, non indexé
   EMAIL : Texte, taille 200, non indexé
   AGE : Entier long, non indexé
2. Créer une table avec une requête SQL

Onglet Requête :

1. Cliquez sur le bouton Nouveau. Deux fenêtres apparaissent.
2. Fermer la fenêtre « Ajouter une table ».
3. Dans la fenêtre :« Requête : Requête Sélection », bouton droit, menu contextuel, premier choix : « Mode SQL ».
4. Dans la fenêtre texte, copier / coller la requête (cf. D.1).
5. Fermer la fenêtre, confirmer son enregistrement, et la nommer.
6. Double-cliquer sur cette nouvelle requête pour exécuter le code SQL.

Notre base Access s'appelle base_test.mdb, elle est située dans le répertoire : C:\base_tutorial. Il faut référencer cette base dans les liaisons DSN du système, pour pouvoir y accéder via ASP ou ADO.

V-B-1. Ouvrir le gestionnaire ODBC▲

V-B-2. Créer une nouvelle source ODBC▲

Article écrit par Olivier Népomiachty, © janvier 2002
e-mail :
Site Internet Delphi : en construction…

Olivier Nepomiachty est le responsable technique historique de developpez.com.

• 2001 à 2005 : responsabilité de l'architecture ainsi que l'exploitation des serveurs GNU/Linux.
• Actuellement évangéliste CRM et speaker à SugarCRM.
• Précédemment 11/2008-2/2012 Technical Architect à Salesforce.com.

LinkedIn : http://www.linkedin.com/in/oliviernepomiachty

Blog #1 : Tortuga CRM (in English)

Blog #2 : Training for Spartathlon (in English)

Twitter : TortugaCrm

Interviews passées 3 février 2005 interview (citations), dossier Economie.net : « Logiciel libre, la nouvelle effervescence des bases de données » 28 janvier 2005, n°1055 interview (citations), dossier « Poste de travail sous Linux » 12 novembre 2004, n°1046 interview (citations), dossier « Les collectivités à l'avant-garde de l'Open Source »