I. Introduction▲
Lors de la saisie de données, il arrive régulièrement qu'une valeur puisse être déterminée selon plusieurs propriétés qui la caractérisent. On peut être ainsi amené à choisir :
- une localité selon le code postal ou le nom de la localité ;
- un article selon son code ou son libellé ;
- un contact selon son nom ou son prénom ;
- un pays selon son code ISO ou son nom ;
- etc.
Access permet de gérer ces cas de figure très facilement, grâce à des outils de création d'interfaces permettant de mettre en place une ergonomie de saisie optimale. En complément, nous verrons comment récupérer une valeur d'une table liée pour l'afficher dans un formulaire.
Pour arriver à nos fins, nous devrons bien entendu nous concentrer d'abord sur la conception de la base de données, clé de voûte de l'application Access. Une base de données bien structurée nous permettra de gagner du temps dans le développement de l'interface, de profiter au mieux des outils Access, de réduire et faciliter les encodages tout en pérennisant les données.
Nous découvrirons aussi que la maîtrise des outils Access tels que requêtes ou listes déroulantes fait partie intégrante du bagage à posséder pour développer rapidement une application de gestion de données.
II. Objectifs et moyens▲
II-A. Objectifs▲
Notre objectif est de mettre en place une ergonomie permettant à l'utilisateur de saisir au sein d'un formulaire une donnée selon l'une ou l'autre de ses caractéristiques, sans utiliser de VBA.
Accessoirement, nous revisiterons :
- certaines propriétés de colonne d'une table ;
- les notions d'intégrité référentielle ;
- les relations entre tables ;
- le concept de clé primaire et clé externe ;
- la création de requêtes ;
- le paramétrage de listes déroulantes ;
- certaines différences entre « moteur de données » et « création d'interface ».
II-B. Moyens▲
Pour arriver à nos fins, nous mettrons en place une gestion de contacts avec nom, prénom, adresse, code postal, localité et pays.
En partant d'une nouvelle base de données vide, nous créerons les tables comprenant clé primaire, clé(s) externe(s) et index, ainsi qu'un formulaire pour gérer les données des contacts. Ce formulaire contiendra des listes déroulantes alimentées par des requêtes.
Ainsi, l'air de rien, nous balayerons quelques notions fondamentales d'Access en mode rappel.
Le but étant de maîtriser la sélection d'une donnée sur base de plusieurs listes déroulantes, nos exemples seront volontairement simples, mais non simplistes.
Le tutoriel ne constitue pas en lui-même une formation complète à la gestion de contacts avec Access, pas plus qu'une technique de gestion d'adresses postales s'appuyant sur des spécificités nationales.
II-C. Version utilisée pour ce tutoriel▲
La version utilisée pour ce tutoriel est Access 2010. Néanmoins, au-delà des différences dues aux interfaces spécifiques à une version, tout ce qui est mis en œuvre ici peut être utilisé avec toute version du logiciel depuis la version 97.
II-D. Niveau, prérequis▲
Dans la mesure où je ne fais que rappeler les concepts de base, une certaine maîtrise des notions élémentaires de conception de base de données et des outils Access permettra de profiter pleinement de ce tutoriel (notamment les notions d'intégrité référentielle et de jointure, ainsi que la manipulation de l'interface Access pour la création des tables, requêtes et formulaires).
Le débutant en Access risque d'être un peu perdu, mais pourra néanmoins tirer profit de ce tutoriel sans trop de problèmes.
III. Tables et propriétés▲
III-A. Conventions de nommage▲
Les conventions suivantes sont d'application :
- les tables sont préfixées par t_ ;
- elles reprennent le nom de l'entité au singulier ;
- hormis les clés externes, un champ est préfixé de l'entité au singulier ;
- la clé primaire est constituée du nom de l'entité suffixé par _PK ;
- la clé primaire est toujours un entier long incrémenté automatiquement (type NuméroAuto) ;
- par conséquent, les clés externes sont de type entier long ;
- les clés externes sont constituées du nom de l'entité externe suffixé par _FK ;
- les index sur plusieurs colonnes sont constitués des noms des entités, collés sans espaces.
Les suffixes _PK et _FK utilisés signifient respectivement Primary Key (clé primaire) et Foreign Key (clé étrangère, clé secondaire ou clé externe).
III-B. Pays▲
Dans cette table, outre la clé primaire, nous trouverons simplement la colonne du nom du pays (voir Fig.1).
Pour les besoins du tutoriel, nous la remplissons avec deux données (voir Fig. 2).
III-C. Localités▲
A minima, la localité sera définie par sa clé primaire, le code postal, le nom de la localité et la clé externe pointant vers le pays (voir Fig.3).
Nous devons empêcher la saisie de doublons dans cette table. Effectivement, cela poserait problème de trouver deux fois 75000 Paris - France ou 4910 Theux - Belgique dans la table.
Pour réaliser cela, nous allons placer un index calculé sur plusieurs colonnes, en refusant les doublons. Les colonnes concernées sont CityZip et CityName. En mode création, nous cliquons sur le ruban Outils de table > Création > Afficher/Masquer > Index (voir Fig.4).
Dans la fenêtre qui s'ouvre, nous voyons la clé primaire, et nous pouvons saisir le nom et les colonnes de l'index que nous voulons ajouter. N'oubliez pas de basculer la propriété Unique à Oui, puisque nous voulons empêcher la saisie de doublons sur les paires CityZip-CityName (voir Fig.5).
Pour créer un index multicolonne, on laisse le champ Nom de l'index vide, sauf sur la première colonne de l'index.
Pour faciliter la saisie du pays dans la table des localités, nous allons placer une liste déroulante sur la colonne Country_FK. Cela s'effectue assez simplement. Après avoir sélectionné la colonne concernée, il suffit d'ouvrir l'onglet Liste de choix, de choisir Zone de liste déroulante, puis de cliquer sur le bouton à droite de Contenu pour ouvrir le générateur de requête (voir Fig. 6).
Dans le générateur, il suffit d'ajouter la table des pays, de sélectionner les deux colonnes en veillant à mettre la colonne Country_PK en première position, puis de trier par ordre croissant des noms de pays (voir Fig. 7).
Bien que la colonne Country_PK ne sera pas affichée, nous devons la prendre dans la requête et, par mesure de facilité, la placer en première position. C'est elle qui constituera la colonne liée, c'est-à-dire celle qui contiendra la valeur qui sera effectivement stockée dans la colonne Country_FK de la table des localités, indépendamment de la valeur affichée dans le contrôle.
Nous fermons l'interface de création en acceptant la mise à jour. Il reste quelques propriétés de la colonne à préciser, à savoir Nbre colonnes, Largeurs colonnes et Largeur liste (voir Fig. 8)
Remarquons la propriété Colonne liée qui vaut 1. Cela signifie que c'est la première colonne de la liste, c'est-à-dire Country_PK dans ce cas-ci, qui contiendra la valeur qui sera renvoyée dans la colonne Country_FK de l'enregistrement actif de la table des localités.
Remarquons également la propriété Largeurs colonnes qui vaut 0cm;4cm, ce qui signifie que la première colonne aura une largeur de 0 cm. Elle sera donc « affichée », mais avec une largeur nulle, et donc invisible.
Remarquons aussi que Limiter à liste, par défaut à Non, a basculé à Oui car la colonne liée est de largeur nulle. Comme on ne sait pas ajouter une valeur dans cette colonne puisqu'elle est masquée, Access nous interdit « officiellement » d'y saisir des données.
Dans l'interface de création de la requête, nous devons laisser la case Afficher pour la colonne Country_PK. Le terme est malheureux, mais cela veut dire que la colonne sera disponible dans la liste déroulante qui appellera la requête, alors même qu'elle ne sera pas visible.
Nous pouvons maintenant saisir les localités et leur attribuer un pays (voir Fig. 9).
Dans la table des localités, la valeur stockée dans la colonne est bien la clé primaire du pays attaché à la localité, bien que ce soit le nom du pays qui est affiché, grâce aux propriétés de la liste déroulante (voir Fig. 10).
Normalement, les données ne sont jamais saisies directement dans les tables. C'est au travers des formulaires que l'utilisateur visualisera, ajoutera, supprimera ou modifiera des données. Dans le cadre de ce tutoriel, il n'était pas nécessaire de passer par cette construction pour les tables t_Country et t_City.
III-D. Contacts▲
Nous définirons un contact uniquement par les colonnes nécessaires à la compréhension du tutoriel (voir Fig.11).
Afin d'éviter trop de modifications au sein du formulaire, notamment en ce qui concerne les étiquettes des contrôles de saisie, il est intéressant de compléter la légende des champs qui seront utilisés dans le formulaire. Ainsi, l'assistant de création du formulaire utilisera la légende comme étiquette du contrôle plutôt que le nom de la colonne (voir Fig. 12).
III-E. Intégrité référentielle et jointures par défaut▲
III-E-1. Intégrité référentielle▲
L'intégrité référentielle permet de garantir qu'une valeur mentionnée dans une clé externe est présente comme clé primaire dans la table liée. Ainsi, pour un contact, il faut interdire le référencement d'une localité qui n'existe pas dans la table des localités. De même, tant qu'une localité est référencée dans la table des contacts, il faut interdire sa suppression dans la table des localités.
Cette protection est placée sur le moteur de données, de sorte qu'elle s'applique à tout consommateur des données.
III-E-2. Jointures par défaut▲
Lors du placement de l'intégrité référentielle, Access place automatiquement une jointure par défaut. Cette jointure est inner join, ce qui implique que seules les lignes des deux tables ayant une paire FK-PK complète seront récupérées par la requête.
Il faut noter que, bien que placée grâce au même outil que l'intégrité référentielle, la jointure par défaut est placée dans le fichier Access, et son périmètre d'action est donc ledit fichier. Les clients externes de la base de données ne peuvent pas « en profiter ».
III-E-3. Placement de l'intégrité référentielle et de la jointure par défaut▲
Nous accédons à la fenêtre des relations via l'onglet du ruban Outils de base de données >
Relations > Relations. À la première ouverture de la fenêtre, la liste des tables de la base apparaît.
Si elle n'apparaît pas, nous y accédons via le ruban Outils de relation >
Relations > Afficher la table. Nous pouvons aussi glisser-déplacer les tables
au départ du volet des objets Access ou procéder par clic droit dans la fenêtre des liaisons (voir Fig. 13).
Pour placer l'intégrité référentielle, faisons glisser la clé externe d'une table vers la clé primaire de la table correspondante (voir Fig. 13). Ensuite, cochons la case Appliquer l'intégrité référentielle. Deux options de l'intégrité peuvent être précisées ici :
Mettre à jour en cascade, qui n'est pas utile ici puisque la clé primaire est de type Numéroauto ;
Supprimer en cascade, qui revient à supprimer les contacts liés à la localité qui sera supprimée. On imagine aisément la catastrophe que cela créerait dans les tables, puisque ce ne sont pas simplement les clés externes qui seront supprimées, mais tous les enregistrements de la table des contacts liés à la localité supprimée. Cette case ne sera cochée que dans certaines circonstances particulières (suppression d'une facture et de ses lignes de détail, par exemple, maintenance de tables…) (voir Fig. 14).
Par défaut, Access place une jointure inner join. Toute nouvelle requête liant ces deux tables utilisera cette jointure par défaut, ce qui aura pour conséquence que, sans modification manuelle de la jointure, les contacts sans localité ne seront pas ramenés par la requête. Je vous conseille donc de modifier cette jointure par défaut. Pour cela, cliquez sur Type jointure… et choisissez la jointure qui permettra de ramener tous les contacts (voir Fig. 15).
Après avoir cliqué sur OK puis sur Créer, vous pouvez visualiser l'intégrité référentielle et la jointure par défaut.
L'intégrité est mentionnée par les sigles infini-1. La jointure est matérialisée par la ligne reliant les deux champs. La flèche côté City_PK signifie que la jointure ramènera tous les contacts.
Vous procéderez de la même façon pour placer l'intégrité et la jointure par défaut entre les tables t_City et t_Country, après quoi vous fermerez la fenêtre des relations en enregistrant les modifications (voir Fig. 16).
IV. Formulaire de gestion du contact▲
IV-A. Création du formulaire▲
Comme dit plus haut, nous ne saisissons normalement pas les données directement dans les tables, mais dans les formulaires. Nous allons donc passer à la création du formulaire de gestion d'un contact.
Pour créer rapidement un formulaire basé sur la table des contacts, il suffit de sélectionner la table, puis de cliquer sur le ruban > Créer > Formulaires > Formulaire.
Access crée automatiquement le formulaire et l'affiche pour la saisie des données (voir Fig. 17).
Il sera affiché en mode Page ou mode Formulaire, selon les options de la base active. Vous basculerez en mode création par clic droit sur l'onglet de formulaire (voir Fig. 18). Observez que le formulaire a utilisé les légendes des colonnes (voir Fig. 19).
IV-B. Ajout et adaptation de contrôles▲
IV-B-1. Mise en place des listes déroulantes▲
Puisque nous voulons deux listes déroulantes, l'une permettant la saisie de la localité par le code postal, l'autre par le nom, nous allons d'abord transformer le contrôle texte CityZip en liste déroulante, par clic droit (Fig. 20).
Nous allons ensuite placer une deuxième liste déroulante, juste à côté de la première, et supprimer son étiquette, celle-ci ne nous servant à rien. Nous ajusterons également les largeurs et alignements des contrôles (voir Fig. 21).
Nous devons maintenant faire pointer la liste déroulante ajoutée, qui est pour l'instant un contrôle indépendant, vers le champ City_FK. Cela est réalisé par la feuille des propriétés ou par saisie directe dans la zone du champ (voir Fig. 22).
Nous avons donc bien deux contrôles qui pointent vers une même colonne de la table, et la modification de la sélection de l'un entraînera automatiquement l'adaptation de l'autre (voir Fig. 23).
IV-B-2. Création des requêtes qui alimenteront les listes déroulantes▲
Nous devons alimenter les listes déroulantes pour que l'utilisateur puisse opérer son choix. Sur le fond, nous procédons de la même manière que pour la création de la liste déroulante des pays dans la table des localités, et nous devrons adapter les mêmes propriétés. Cependant, l'organisation de ces propriétés est différente en mode Édition de formulaire, certaines propriétés se trouvant sur l'onglet Format et d'autres étant placées sur l'onglet Données.
Il faut modifier la propriété Contenu de l'onglet Données dans la feuille de propriété du contrôle (voir Fig.24).
Nous allons ajouter la table t_City pour sélectionner les colonnes des tables dont nous avons besoin, et nous définirons les ordres de tri des enregistrements, c'est-à-dire d'abord sur le code postal, puis sur le nom de la localité. La colonne City_PK ne sera pas visible car sa largeur sera nulle. La première colonne visible sera donc CityZip, et c'est donc sur cette colonne que la sélection pourra être réalisée (voir Fig. 25).
Rappel : La colonne City_PK doit être prise dans la requête puisque c'est elle qui contiendra la valeur qui sera stockée dans la colonne City_FK de la table des contacts.
Bien entendu, nous procéderons de même pour la deuxième liste déroulante, mais en inversant l'ordre des colonnes présentées par la requête, pour permettre de sélectionner la localité sur base du nom (voir Fig. 26).
Cette mise en place va permettre de sélectionner la ville par le code postal ou par la localité.
V. Utilisation du formulaire▲
L'utilisation du formulaire ne demande pas d'explications particulières. Il suffit de choisir la commune selon le code postal ou la localité pour l'attribuer à la fiche du contact. La sélection dans une des deux colonnes modifie automatiquement la valeur de l'autre, puisqu'elles pointent toutes les deux vers City_FK de la table des contacts (voir Fig. 27 à 30).
Grâce à cette approche, nous avons donc amélioré l'ergonomie de notre application, en permettant la saisie de données selon deux listes déroulantes. Mission accomplie !
VI. Affichage d'une donnée issue d'une table liée : le nom du pays▲
L'utilisateur souhaiterait pouvoir afficher le pays lié à la localité. Il n'est bien entendu pas question qu'il doive sélectionner le pays lui-même, ce qui pourrait amener à des incohérences dans les tables. En effet, un Theutois pourrait par inadvertance devenir Français alors qu'un Parisien pourrait devenir Belge à la suite d'une erreur d'encodage.
Lors de la création des tables, nous avons créé une colonne Country_FK dans la table t_City liée à la colonne Country_PK de la table des pays, de manière à ne devoir définir qu'une seule fois un pays pour une localité donnée.
Nous allons nous servir de cette structure pour afficher automatiquement le pays dans le formulaire du contact, lors de la saisie de la localité.
Il existe au moins trois façons de réaliser cela. J'explique ici la plus simple des trois, celle qui demandera le moins de manipulations dans le formulaire.
VI-A. Modification de la source de données du formulaire▲
Pour afficher le nom du pays lié à la localité du contact, il faut d'abord que la colonne CountryName soit présente dans la requête sous-jacente. En mode création, vous allez accéder aux propriétés du formulaire et afficher l'onglet Données, puis ouvrir le générateur de requête en cliquant sur … à droite de la propriété Source (voir Fig. 31).
Ajoutez les tables t_City et t_Country. Ici, la table t_City, bien que nous n'en utiliserons pas les champs dans le formulaire, est indispensable à la requête, puisqu'elle sert de passerelle entre les tables t_Contact et t_Country. Il n'y a en effet aucun lien direct entre ces deux tables. Vous visualisez les jointures par défaut, qui vont permettre de ramener dans la requête les lignes comprenant toutes les données liées. Ajoutez le champ CountryName aux colonnes de la requête, par exemple en double-cliquant dessus (voir Fig. 32). Fermez la fenêtre en enregistrant les modifications.
Le formulaire dispose maintenant d'une nouvelle colonne, visible dans la liste des champs (voir Fig. 33).
Vous allez placer le champ dans le formulaire par glisser-déplacer, puis supprimer son étiquette (voir Fig. 34).
Vous devez absolument verrouiller le contrôle pour en interdire toute modification. Ce champ pointe vers la colonne CountryName de la table t_Country. Une modification à ce niveau modifierait donc le nom du pays dans la table des pays. Toute localité, et par ricochet, tout contact lié seraient affectés par cette modification malencontreuse (voir Fig. 35).
Lors de la visualisation, la saisie ou la modification d'une fiche de contact, vous pouvez maintenant voir le nom du pays de résidence de ce contact (voir Fig. 36).
VII. Conclusions▲
Ce tutoriel a atteint son objectif. Vous pouvez maintenant sélectionner facilement une donnée selon plusieurs de ses paramètres.
J'ai également souhaité, dans ce tutoriel, vous montrer l'importance d'une bonne conception de base de données, et la facilité avec laquelle vous pouvez manipuler les outils d'Access pour mettre en place à peu de frais et peu d'efforts, une ergonomie conviviale tout en sécurisant vos données.
VIII. Remerciements▲
Pour l'idée du tuto, merci à Claude Leloup (ClaudeLeloup)
Pour la relecture technique, je remercie Fabien (f-leb).
La relecture orthographique a été assurée par Claude Leloup (ClaudeLeloup) que je remercie vivement.