Création d'un journal d'événements sous Access 2010

Christophe WARIN

Cet article destiné aux habitués de Microsoft Access connaissant les bases du langage VBA et du langage SQL vous propose un moyen de mettre en place un système de contrôle des données avant leur enregistrement en se basant sur un mécanisme propre à quasiment tous les SGBD, mais un peu caché sous Microsoft Access.

Commentez

- Titre : Création d'un journal d'événements sous Access 2010

- Auteur : Christophe WARIN

- Parution : 7 septembre 2009

- Mise à jour : 16 février 2014

- Licence : Copyright ® 2009 Christophe Warin. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. Introduction

- II. La table USysApplicationLog

- III. Journalisation avancée

- III-A. Insertion dans la table Evenement

- III-B. Insertion dans la table DetailEvenement

- IV. Limitation de la taille du journal

- V. Conclusion

I - Introduction

La création d'un journal dans une base Access a toujours été une question récurrente sur les forums. Le premier obstacle rencontré jusqu'à Access 2010 était l'absence de trigger (déclencheur) sur les tables. Comment enregistrer le fait qu'une information ait été modifiée si aucun mécanisme ne signale ce changement ? Deuxième obstacle : la taille du journal. Avec une base de petite taille, mais où les accès en écriture sont nombreux, le fichier journal atteint vite un volume monstrueux nécessitant une purge manuelle ou la mise en place d'un système de surveillance chargé de purger vider régulièrement les historiques obsolètes. Bien entendu, être obligé de coder un espion sur un autre est peu attractif. Heureusement, avec l'arrivée des événements de tables que l'on peut aussi appeler déclencheurs où trigger (à l'instar des SGBD plus robustes), Microsoft Access 2010 enfonce encore un peu plus le clou de l'application professionnelle.

Dans ce tutoriel, nous allons tenter de répondre au cahier des charges suivant :

Enregistrer les modifications apportées à la table Clients ci-dessous :

Si vous n'avez jamais manipulé les macros de données (DataMacro), je vous invite à commencer par un autre article de découverte : https://warin.developpez.com/tutoriels/access/access2010/nouveautes/?page=page_2#LII

II - La table USysApplicationLog

Cette table est mentionnée dans quasiment tous les documents traitant des macros de données pour la simple et bonne raison que c'est ici que seront stockés les messages d'erreurs des événements de tables ayant échoué.

Si vous ne voyez pas cette table, rendez-vous dans les options du panneau de navigation afin d'activer l'affichage des objets système.

La méthode LogEvent des DataMacros offre au développeur la possibilité d'enrichir cette table avec des événements personnalisés. Par exemple, sur l'événement Après MAJ (AfterUpdate) de la table Clients :

LogEvent
Description : Modification d'un enregistrement dans la table Clients.

L'attribut Description est de type texte. Pour y insérer une donnée provenant d'un champ de la table il est nécessaire de passer par une expression calculée :

LogEvent
Description : ="Modification d'un enregistrement dans la table Clients. IDClient=" & [ID]

Le sigle = placé en tête de l'attribut demande au moteur de base de données d'évaluer l'expression composée de deux variables : le texte et l'id du client.

Si cette solution a le mérite d'être très simple, elle reste cependant déconseillée puisque les événements d'erreurs se retrouvent mélangés à un simple historique. D'un point de vue purement conceptuel il parait anormal de stocker dans la même structure des données système et de données métier. Jamais il ne vous prendrait l'idée de stocker vos photos dans le répertoire System32 de Windows au beau milieu de centaines de dll.

III - Journalisation avancée

Comme la table USysApplicationLog semble insuffisante, nous allons créer notre propre structure de journalisation. Celui-ci est constitué de deux tables et permettra de lister les valeurs des champs avant et après mise à jour.

Table Evenement :

- IDEvenement : NuméroAuto - Clé primaire
Identifiant de la table ;

- DateEvenement : Date/Heure - Valeur par défaut : Now()
Date où il y a eu lieu l'événement ;

- TableEvenement : Texte
Nom de la table où a eu lieu l'événement ;

- EnregistrementEvenement : Numérique
Identifiant de l'enregistrement qui a été modifié ;

- TypeEvenement : Texte
Suppression, modification ou insertion.

Table DetailEvenement :

- IDDetailEvenement : NuméroAuto - Clé primaire
Identifiant de la table ;

- ChampDetailEvenement : Texte
Nom du champ modifié ;

- AncienDetailEvenement : Texte
Ancienne valeur ;

- NouveauDetailEvenement : Texte
Nouvelle valeur ;

- IDEvenement : Numérique
Clé étrangère correspondant à l'événement dans la table Evenement.

L'insertion d'un nouvel élément dans le journal se déroule en plusieurs étapes :

1. Création d'une nouvelle ligne dans la table Evenement dès qu'un client est mis à jour ;

2. Création d'une nouvelle ligne dans la table DetailEvenement pour chaque champ concerné par cette modification.

III-A - Insertion dans la table Evenement

Pour que l'ajout d'une entrée dans le journal ait lieu à chaque modification d'un client, il faut se placer sur l'événement Après MAJ de la table Clients.

La valeur par défaut du champ nous soulage du traitement de la date.

Créer un enregistrement dans   Evenement
Alias  RecordEvenement

DéfinirChamp
Nom    EnregistrementEvenement
Valeur    =[Clients].[Id]

DéfinirChamp
Nom    TableEvenement
Valeur    ="Clients"

DéfinirChamp
Nom    TypeEvenement
Valeur    ="UPDATE"

Version XML :

<?xml version="1.0" encoding="iso-8859-1" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
<DataMacro Event="AfterUpdate">
<Statements>
<CreateRecord>
<Data Alias="RecordEvenement">
<Reference>Evenement</Reference>
</Data>
<Statements>
<Action Name="SetField">
<Argument Name="Field">EnregistrementEvenement</Argument>
<Argument Name="Value">[Clients].[Id]</Argument>
</Action>
<Action Name="SetField">
<Argument Name="Field">TableEvenement</Argument>
<Argument Name="Value">"Clients"</Argument>
</Action>
<Action Name="SetField">
<Argument Name="Field">TypeEvenement</Argument>
<Argument Name="Value">"UPDATE"</Argument>
</Action>
</Statements>
</CreateRecord>
</Statements>
</DataMacro>
</DataMacros>

La syntaxe demande un peu de rigueur notamment en ce qui concerne la portée des membres. Dans le premier SetField, la valeur [ID] en remplacement de [Clients].[ID] aurait levé une erreur dans la table USysApplicationLog car le moteur s'attend à recevoir un champ de l'enregistrement RecordEvenement. Une autre solution serait de passer par une variable locale dans la racine de la DataMacro :

DéfinirVarLocale
Nom    varIdClient
Exp    =[Clients].[Id]

Créer un enregistrement dans   Evenement
Alias  RecordEvenement

DéfinirChamp
Nom    EnregistrementEvenement
Valeur    =varIdClient

DéfinirChamp
Nom    TableEvenement
Valeur    ="Clients"

DéfinirChamp
Nom    TypeEvenement
Valeur    ="UPDATE"

Version XML :

<?xml version="1.0" encoding="iso-8859-1" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
<DataMacro Event="AfterUpdate">
<Statements>
<Action Name="SetLocalVar">
<Argument Name="Name">varIdClient</Argument>
<Argument Name="Value">[Clients].[Id]</Argument>
</Action>
<CreateRecord>
<Data Alias="RecordEvenement">
<Reference>Evenement</Reference>
</Data>
<Statements>
<Action Name="SetField">
<Argument Name="Field">EnregistrementEvenement</Argument>
<Argument Name="Value">[varIdClient]</Argument>
</Action>
<Action Name="SetField">
<Argument Name="Field">TableEvenement</Argument>
<Argument Name="Value">"Clients"</Argument>
</Action>
<Action Name="SetField">
<Argument Name="Field">TypeEvenement</Argument>
<Argument Name="Value">"UPDATE"</Argument>
</Action>
</Statements>
</CreateRecord>
</Statements>
</DataMacro>
</DataMacros>

Cette solution offre l'avantage d'afficher clairement en entête de macro les éléments qui seront réutilisés par la suite.

III-B - Insertion dans la table DetailEvenement

Pour chaque champ de la table Clients, la macro de données va devoir vérifier si celui-ci a été modifié ou non. Dans le cas où un changement a été repéré, une nouvelle ligne sera ajoutée dans la table DetailEvenement regroupant l'ancienne et la nouvelle valeur. Pour cela, deux fonctionnalités doivent être utilisées :

- La fonction Updated(nomduchamp) qui permet de détecter si le champ passé en paramètre a été modifié ;

- La syntaxe Old.NomDuChampqui permet de récupérer l'ancienne valeur d'un champ modifié.

Dans un premier temps il est surtout question de la jointure entre les deux tables du journal. Il est nécessaire d'affecter aux lignes qui vont être définies l'identifiant de l'événement nouvellement créé accessible via [IdentitéDernierEnregistrementCréé]. Le recours à une variable locale semble approprié à condition de placer l'appel de SetLocalVar juste après le bloc CreateRecord.

DéfinirVarLocale
Nom    varIdClient
Exp    =[Clients].[Id]

Créer un enregistrement dans   Evenement
Alias  RecordEvenement

DéfinirChamp
Nom    EnregistrementEvenement
Valeur    =varIdClient

DéfinirChamp
Nom    TableEvenement
Valeur    ="Clients"

DéfinirChamp
Nom    TypeEvenement
Valeur    ="UPDATE"

DéfinirVarLocale
Nom    varIdEvenement
Exp    =[IdentitéDernierEnregistrementCréé]

Cette formalité accomplie, il faut, pour chaque champ, répéter le même bloc d'instructions chargées d'alimenter la table DetailEvenement :

Si Updated("reference") Alors

Créer un enregistrement dans   DetailEvenement
Alias  RecordDetailEvenement

DéfinirChamp
Nom    ChampDetailEvenement
Valeur    "reference"

DéfinirChamp
Nom    AncienDetailEvenement
Valeur    [Old].[reference]

DéfinirChamp
Nom    NouveauDetailEvenement
Valeur    [Clients].[reference]

DéfinirChamp
Nom    IdEvenement
Valeur    [varIdEvenement]
Fin Si

Version XML :

<?xml version="1.0" encoding="iso-8859-1" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
<DataMacro Event="AfterUpdate">
<Statements>
<Action Name="SetLocalVar">
<Argument Name="Name">varIdClient</Argument>
<Argument Name="Value">[Clients].[Id]</Argument>
</Action>
<CreateRecord>
<Data Alias="RecordEvenement">
<Reference>Evenement</Reference>
</Data>
<Statements>
<Action Name="SetField">
<Argument Name="Field">EnregistrementEvenement</Argument>
<Argument Name="Value">[varIdClient]</Argument>
</Action>
<Action Name="SetField">
<Argument Name="Field">TableEvenement</Argument>
<Argument Name="Value">"Clients"</Argument>
</Action>
<Action Name="SetField">
<Argument Name="Field">TypeEvenement</Argument>
<Argument Name="Value">"UPDATE"</Argument>
</Action>
</Statements>
</CreateRecord>
<Action Name="SetLocalVar">
<Argument Name="Name">varIdEvenement</Argument>
<Argument Name="Value">[LastCreateRecordIdentity]</Argument>
</Action>
<ConditionalBlock>
<If>
<Condition>Updated("reference")</Condition>
<Statements>
<CreateRecord>
<Data Alias="RecordDetailEvenement">
<Reference>DetailEvenement</Reference>
</Data>
<Statements>
<Action Name="SetField">
<Argument Name="Field">ChampDetailEvenement</Argument>
<Argument Name="Value">"reference"</Argument>
</Action>
<Action Name="SetField">
<Argument Name="Field">AncienDetailEvenement</Argument>
<Argument Name="Value">[Old].[reference]</Argument>
</Action>
<Action Name="SetField">
<Argument Name="Field">NouveauDetailEvenement</Argument>
<Argument Name="Value">[clients].[Reference]</Argument>
</Action>
<Action Name="SetField">
<Argument Name="Field">IdEvenement</Argument>
<Argument Name="Value">[varIdEvenement]</Argument>
</Action>
</Statements>
</CreateRecord>
</Statements>
</If>
</ConditionalBlock>
</Statements>
</DataMacro>
</DataMacros>

Bien entendu, il paraît fastidieux de devoir dupliquer ce bloc If pour chaque champ de la table Clients. Tout comme en VBA, il est possible de factoriser le code en isolant le bloc conditionnel dans une macro dédiée et en faisant appel à cette macro depuis l'événement Après MAJ.

Schématiquement, cela donne la macro dm_detailEvenement et les appels suivants :

dm_DetailEvenement(pNomChamp,pAncienneValeur,pNouvelleValeur,pID)

Les paramètres correspondent respectivement au nom du champ concerné, son ancienne valeur, sa nouvelle valeur et l'identifiant de l'événement courant.

Cette macro est ensuite appelée dans l'événement de la table :

...
ExécuterMacroDonnées
dm_DetailEvenement("Reference",[Old].[Reference],[Reference],[varIdEvenement])

ExécuterMacroDonnées
dm_DetailEvenement("Societe",[Old].[Societe],[Societe],[varIdEvenement])
...

Soit en langage Macro :

dm_DetailEvenement :

Paramètres
Nom   pNomChamp
Nom   pAncienneValeur
Nom   pNouvelleValeur
Nom   pId

Si Updated(pNomChamp) Alors

Créer un enregistrement dans   DetailEvenement
Alias  RecordDetailEvenement

DéfinirChamp
Nom    ChampDetailEvenement
Valeur    pNomChamp

DéfinirChamp
Nom    AncienDetailEvenement
Valeur    pAncienneValeur

DéfinirChamp
Nom    NouveauDetailEvenement
Valeur    pNouvelleValeur

DéfinirChamp
Nom    IdEvenement
Valeur    pId
Fin Si

Version XML :

<?xml version="1.0" encoding="iso-8859-1" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
<DataMacro Name="dm_DetailEvenement">
<Parameters>
<Parameter Name="pNomChamp" />
<Parameter Name="pAncienneValeur" />
<Parameter Name="pNouvelleValeur" />
<Parameter Name="pID" />
</Parameters>
<Statements>
<ConditionalBlock>
<If>
<Condition>Updated([pNomChamp])</Condition>
<Statements>
<CreateRecord>
<Data Alias="RecordDetailEvenement">
<Reference>DetailEvenement</Reference>
</Data>
<Statements>
<Action Name="SetField">
<Argument Name="Field">ChampDetailEvenement</Argument>
<Argument Name="Value">[pNomChamp]</Argument>
</Action>
<Action Name="SetField">
<Argument Name="Field">AncienDetailEvenement</Argument>
<Argument Name="Value">[pAncienneValeur]</Argument>
</Action>
<Action Name="SetField">
<Argument Name="Field">NouveauDetailEvenement</Argument>
<Argument Name="Value">[pNouvelleValeur]</Argument>
</Action>
<Action Name="SetField">
<Argument Name="Field">IdEvenement</Argument>
<Argument Name="Value">[pId]</Argument>
</Action>
</Statements>
</CreateRecord>
</Statements>
</If>
</ConditionalBlock>
</Statements>
</DataMacro>
</DataMacros>

Attention : la variable locale varIdEvenement du trigger AfterUpdate a une portée limitée à cet événement et n'est pas disponible dans la nouvelle macro dm_DetailEvenement. Il est donc nécessaire de la passer en paramètre.

Clients.AfterUpdate :

DéfinirVarLocale
Nom    varIdClient
Exp    =[Clients].[Id]

Créer un enregistrement dans   Evenement
Alias  RecordEvenement

DéfinirChamp
Nom    EnregistrementEvenement
Valeur    =varIdClient

DéfinirChamp
Nom    TableEvenement
Valeur    ="Clients"

DéfinirChamp
Nom    TypeEvenement
Valeur    ="UPDATE"

DéfinirVarLocale
Nom    varIdEvenement
Exp    =[IdentitéDernierEnregistrementCréé]

ExécuterMacroDonnées
Nom de la Macro   dm_DetailEvenement
Paramètres
pNomChamp          "reference"
pAncienneValeur    [Old].[Reference]
pNouvelleValeur    [Clients].[Reference]
pId                [varIdEvenement]

ExécuterMacroDonnées
Nom de la Macro   dm_DetailEvenement
Paramètres
pNomChamp          "societe"
pAncienneValeur    [Old].[societe]
pNouvelleValeur    [Clients].[societe]
pId                [varIdEvenement]

...

Version XML :

<?xml version="1.0" encoding="iso-8859-1" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
<DataMacro Event="AfterUpdate">
<Statements>
<Action Name="SetLocalVar">
<Argument Name="Name">varIdClient</Argument>
<Argument Name="Value">[Clients].[Id]</Argument>
</Action>
<CreateRecord>
<Data Alias="RecordEvenement">
<Reference>Evenement</Reference>
</Data>
<Statements>
<Action Name="SetField">
<Argument Name="Field">EnregistrementEvenement</Argument>
<Argument Name="Value">[varIdClient]</Argument>
</Action>
<Action Name="SetField">
<Argument Name="Field">TableEvenement</Argument>
<Argument Name="Value">"Clients"</Argument>
</Action>
<Action Name="SetField">
<Argument Name="Field">TypeEvenement</Argument>
<Argument Name="Value">"UPDATE"</Argument>
</Action>
</Statements>
</CreateRecord>
<Action Name="SetLocalVar">
<Argument Name="Name">varIdEvenement</Argument>
<Argument Name="Value">[LastCreateRecordIdentity]</Argument>
</Action>
<Action Name="RunDataMacro">
<Argument Name="MacroName">Clients.dm_DetailEvenement</Argument>
<Parameters>
<Parameter Name="pNomChamp" Value="&quot;reference&quot;" />
<Parameter Name="pAncienneValeur" Value="[Old].[Reference]" />
<Parameter Name="pNouvelleValeur" Value="[Clients].[Reference]" />
<Parameter Name="pID" Value="[varIdEvenement]" />
</Parameters>
</Action>
<Action Name="RunDataMacro">
<Argument Name="MacroName">Clients.dm_DetailEvenement</Argument>
<Parameters>
<Parameter Name="pNomChamp" Value="&quot;societe&quot;" />
<Parameter Name="pAncienneValeur" Value="[Old].[societe]" />
<Parameter Name="pNouvelleValeur" Value="[Clients].[societe]" />
<Parameter Name="pID" Value="[varIdEvenement]" />
</Parameters>
</Action>

...

</Statements>
</DataMacro>
</DataMacros>

IV - Limitation de la taille du journal

La journalisation est à présent opérationnelle et le journal va se remplir à vitesse grand V au point qu'il est primordial d'y appliquer un quota. Cet exemple se base sur une limitation du nombre d'événements dans la table Evenement. L'idée est très simple, lorsque le journal est plein, on le vide par le bas à chaque insertion de sorte à garder un maximum de 10, (20, 100…) événements.

Pour cela, il est nécessaire de travailler sur l'événement Après Insertion (AfterInsert) de la table Evenement et appliquer la suppression en cascade sur la relation Evenement-DetailEvenement de telle sorte à propager la purge aux deux tables.

La solution la plus facile serait de supprimer l'événement le plus ancien à chaque nouvelle insertion dès que le quota est atteint ; il s'agit d'une pile. Seul bémol, en cas de redimensionnement du quota, le mécanisme est complètement déstabilisé. Le choix le plus souple consiste à lister dans une requête les IDEvenement en ordre décroissant et à supprimer les superflus à l'aide d'un compteur (varN). Dans le cas d'un journal à fort volume, le système de pile reste bien entendu préférable, car bien moins gourmand en ressources.

DéfinirVarLocale
Nom    varN
Exp    10

Pour chaque enregistrement dans  qry_Evenement_DESC
Alias RecordEvenement
Where
DéfinirVarLocale
Nom    varN
Exp    [varN]-1

Si [VarN]<0 Alors

SupprimerEnregistrement
Alias d'enregistrement RecordEvenement
Fin Si

Version XML :

<?xml version="1.0" encoding="iso-8859-1" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
<DataMacro Event="AfterInsert">
<Statements>
<Action Name="SetLocalVar">
<Argument Name="Name">varN</Argument>
<Argument Name="Value">10</Argument>
</Action>
<ForEachRecord>
<Data Alias="RecordEvenement">
<Reference>qry_Evenement_DESC</Reference>
</Data>
<Statements>
<Action Name="SetLocalVar">
<Argument Name="Name">varN</Argument>
<Argument Name="Value">[varN]-1</Argument>
</Action>
<ConditionalBlock>
<If>
<Condition>[varN]&gt;0</Condition>
<Statements>
<Action Name="DeleteRecord">
<Argument Name="Alias">RecordEvenement</Argument>
</Action>
</Statements>
</If>
</ConditionalBlock>
</Statements>
</ForEachRecord>
</Statements>
</DataMacro>
</DataMacros>

Code de la requête qry_Evenement_DESC :

SELECT IdEvenement
FROM Evenement
ORDER BY IDEvenement DESC;

En créant une table Parametre (NomParametre, ValeurParametre) contenant l'enregistrement (QuotaJournal, 10) il est tout à fait possible de rendre le journal évolutif : l'utilisateur n'aura qu'à modifier le contenu de la table pour le mettre à jour son quota :

Rechercher un enregistrement dans Parametre
Condition Where  "NomParametre='QuotaJournal'"
Alias            RecordParametre

DéfinirVarLocale
Nom    varN
Exp    [ValeurParametre]

Pour chaque enregistrement dans  qry_Evenement_DESC
Alias RecordEvenement
Where
DéfinirVarLocale
Nom    varN
Exp    [varN]-1

Si [VarN]<0 Alors

SupprimerEnregistrement
Alias d'enregistrement RecordEvenement
Fin Si

Version XML :

<?xml version="1.0" encoding="iso-8859-1" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
<DataMacro Event="AfterInsert">
<Statements>
<LookUpRecord>
<Data Alias="RecordParametre">
<Reference>Parametre</Reference>
<WhereCondition>"NomParametre='QuotaJournal'"</WhereCondition>
</Data>
<Statements>
<Action Name="SetLocalVar">
<Argument Name="Name">varN</Argument>
<Argument Name="Value">[ValeurParametre]</Argument>
</Action>
</Statements>
</LookUpRecord>
<ForEachRecord>
<Data Alias="RecordEvenement">
<Reference>qry_Evenement_DESC</Reference>
</Data>
<Statements>
<Action Name="SetLocalVar">
<Argument Name="Name">varN</Argument>
<Argument Name="Value">[varN]-1</Argument>
</Action>
<ConditionalBlock>
<If>
<Condition>[varN]&gt;0</Condition>
<Statements>
<Action Name="DeleteRecord">
<Argument Name="Alias">RecordEvenement</Argument>
</Action>
</Statements>
</If>
</ConditionalBlock>
</Statements>
</ForEachRecord>
</Statements>
</DataMacro>
</DataMacros>

V - Conclusion

Je dois l'avouer, j'étais un peu réticent aux événements de table lorsque je les ai découverts pour la première fois. Le système de macro me semblait particulièrement lourd et peu attractif. Toujours est-il que l'on y prend goût, le copier-coller de bloc est possible, la factorisation en « sous-macro » aussi. Bref : une nouveauté à utiliser absolument !

Remerciements : phanloga pour ses corrections orthographiques.