IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)

Normaliser les tables Access contenant des champs multivalués

Objectif : normaliser les tables contenant des champs(1) multivalués.

Niveau requis : avancé.

Commentez cet article : Commentez Donner une note à l´article (5)

Article lu   fois.

L'auteur

Profil Pro

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Introduction

Les champs multivaluésLes champs multivalués permettent d'afficher directement dans les tables, les requêtes ou les formulaires, des listes de choix avec des cases à cocher pour sélectionner des données provenant d'une autre source.

Par exemple, on peut choisir les candidats inscrits à l'examen « Algorithmique - Programmation » directement dans la table des examens :

Champs multivalués
Champs multivalués

Cependant, comme ces champs peuvent contenir plusieurs valeurs pour un même enregistrement, ils ne répondent pas à la première forme normale de la théorie de la normalisation, nécessaire pour concevoir un bon schéma d'une base de données. Leur utilisation dans les requêtes comme dans le code peut ainsi sembler déroutante.

Ils ne permettent pas non plus d'enregistrer d'autres informations, comme une date d'inscription à l'examen.

En résumé, ils peuvent par la suite compliquer le développement et la maintenance de la base Access.

Malheureusement, comme on le constate sur le forum Accessforum Access, le développeur a souvent tendance, par commodité, à utiliser ce type de colonne, plutôt que de concevoir une base de données qui respecte un bon schéma relationnelun bon schéma relationnel.

Cependant donc, si par la suite les utilisateurs de la base souhaitent par exemple ajouter une date d'inscription du candidat à l'examen, ce n'est pas possible avec le champ multivalué mis en place.

Pour résoudre ce genre de problèmes, j'ai pensé qu'il serait utile de montrer comment implémenter une fonction permettant d'extraire les valeurs contenues dans ce type de champ pour les enregistrer dans une table intermédiaire permettant de faire le lien entre la table principale et celle qui alimente le champ multivalué.

On supprimera également à la fin la colonne multivaluée de la table, il ne restera alors plus qu'à adapter les autres objets de l'application à cette nouvelle base de données.

II. Tables de départ

On dispose donc d'une table pour enregistrer les informations sur les candidats et d'une autre table pour sauvegarder les données relatives aux examens.

II-A. T_Candidat

Elle permet d'enregistrer les données concernant les candidats.

Nom du champ

Type de données

Description du champ

IdCandidat

NumeroAuto

Identifiant du candidat

NomCandidat

Texte

Nom du candidat

PrenomCandidat

Texte

Prénom du candidat

Cycle

Texte

Cycle de formation du candidat

Annee

Texte

Année scolaire : 2020-2021

II-B. T_Examen

Elle contient les informations relatives aux examens.

Nom du champ

Type de données

Description du champ

IdExamen

NumeroAuto

Identifiant de l'examen

IntituleExamen

Texte

Intitulé de l'examen : « TP Programmation », etc.)

Cycle

Texte

Cycle de formation : « A1 »,  « A2 », etc.

DateExamen

Date/Heure

Date de l'examen

HeureDebutExamen

Date/Heure

Heure de début de l'examen

HeureFinExamen

Date/Heure

Heure de fin de l'examen

Salle

Texte

Identifiant de la salle où a lieu l'épreuve

DateFinInscription

Date/Heure

Date de fin des inscriptions à l'épreuve

Inscrits

Numérique

Champ multivalués permettant d'enregistrer plusieurs candidats à un examen


Aperçu de la table en mode création :

Table en mode création
Table en mode création


Aperçu de quelques données avant l'extraction :

Table en mode création
Aperçu de quelques données

On constate que la dernière colonne multivaluée de la table permet d'enregistrer plusieurs candidats à un examen.

On aura en fait besoin de créer une table supplémentaire pour enregistrer les inscriptions à chaque examen.

III. Fonction d'extraction des valeurs

Elle permet d'extraire les valeurs pour les enregistrer dans une table secondaire.


Arguments de la fonction :

  1. nomTable : nom de la table contenant la colonne multivaluée ;
  2. nomChampID : nom du champ identifiant de la table ;
  3. nomChampMV : nom du champ multivalué ;
  4. nomTable2 : nom de la table contenant les valeurs extraites ;
  5. nomChampVL : nom du champ contenant la valeur.


La fonction ExtraireValeurs renvoie True si l'opération d'extraction s'est bien passée et False dans le cas contraire.


Déroulé de la fonction :

  • 1. ouverture du jeu d'enregistrements basé sur la table contenant le champ multivalué ;
  • 2. création de la table permettant d'enregistrer les valeurs ;
  • 3. parcours des enregistrements de la table contenant le champ multivalué ;
  • ----- 3.1. pour chaque enregistrement : parcours des valeurs contenues dans le champ multivalué,
  • ----------3.1.1 pour chaque valeur : copie dans la table secondaire (table intermédiaire) de cette valeur et de l'identifiant de la table principale,
  • ----------3.1.2 pour chaque valeur : suppression de la valeur de la table ;
  • 4. suppression du champ multivalué de la table principale ;
  • 5. fermeture et libération des variables.


Parties importantes du code

On ouvre le jeu d'enregistrements basé sur la table contenant le champ multivalué, puis on crée la table destinée à enregistrer les valeurs :

 
Sélectionnez
    Set dbs = CurrentDb() ' référence à la base de données courante
 
    Set rst = dbs.OpenRecordset(nomTable) ' ouverture du recordset basé sur la table contenant les pièces jointes
 
    If Not TableExiste(nomTable2) Then  ' si la table destinée à enregistrer les valeurs du champ multivalué n'existe pas
        ' création de la table permettant d'enregistrer les valeurs du champ
        dbs.Execute "create table [" & nomTable2 & "]([" & nomChampVL & "] INTEGER, [" & nomChampID & "] INTEGER);", dbFailOnError  
    Else ' sinon
        dbs.Execute "delete * from [" & nomTable2 & "];", dbFailOnError ' on vide la table si elle existe déjà
    End If


On ouvre le jeu d'enregistrements basé sur la table destinée à enregistrer les valeurs du champ multivalué :

 
Sélectionnez
    ' ouverture du recordset basé sur la nouvelle table destinée à enregistrer les valeurs du champ multivalué
    Set rst2 = dbs.OpenRecordset(nomTable2)


Pour chaque enregistrement de la table principale, on récupère le recordset du champ multivalué et on copie ses valeurs dans la table secondaire avec suppression des valeurs de la table principale :

 
Sélectionnez
    Do Until rst.EOF ' on parcourt les enregistrements de la table
 
        ' on récupère le recordset lié au champ multivalué de l'enregistrement courant
        Set rstMV = rst(nomChampMV).Value
 
        ' on parcourt les valeurs du champ multivalué
        Do Until rstMV.EOF
            
            ' ajout de la valeur à la nouvelle table avec l'identifiant de la table principale
            rst2.AddNew
            rst2.Fields(nomChampVL) = rstMV.Fields(0)
            rst2.Fields(nomChampID) = rst.Fields(nomChampID)
            rst2.Update
 
            rstMV.Delete ' suppression de la valeur contenue dans le champ multivalué
 
            rstMV.MoveNext  ' prochaine valeur
 
        Loop
 
        ' prochain enregistrement de la table principale
        rst.MoveNext
 
    Loop

On suppose pour simplifier que la table ne contient qu'un seul champ multivalué.


Code complet

Module M_Normaliser_Tables
Cacher/Afficher le codeSélectionnez
Public Function ExtraireValeurs(nomTable As String, nomChampID As String, nomChampMV As String, nomTable2 As String, nomChampVL As String) As Boolean
    On Error GoTo err_ExtraireValeurs
    Dim dbs As DAO.Database ' variable objet pour faire référence à la base de données
    Dim tdf As DAO.TableDef ' variable objet pour faire référence à la table contenant le champ multivalués
    Dim rst As DAO.Recordset ' variable recordset pour faire référence à la table
    Dim rstMV As DAO.Recordset ' variable recordset pour faire référence au jeu d'enregistrements du champ multivalués
    Dim rst2 As DAO.Recordset ' variable pour faire référence à la nouvelle table destinée à enregistrer les valeurs associées aux identifiants de la table principale
    
    ' référence à la base de données courante
    Set dbs = CurrentDb()
    
    ' ouverture du recordset basé sur la table contenant les pièces jointes
    Set rst = dbs.OpenRecordset(nomTable)
 
    If Not TableExiste(nomTable2) Then  ' si la table destinée à enregistrer les valeurs du champ multivalué n'existe pas
        ' création de la table permettant d'enregistrer les valeurs du champ
        dbs.Execute "create table [" & nomTable2 & "]([" & nomChampVL & "] INTEGER, [" & nomChampID & "] INTEGER);", dbFailOnError
    Else ' sinon
        dbs.Execute "delete * from [" & nomTable2 & "];", dbFailOnError ' on vide la table si elle existe déjà
    End If
    
    ' ouverture du recordset basé sur la nouvelle table destinée à enregistrer les valeurs du champ multivalué
    Set rst2 = dbs.OpenRecordset(nomTable2)
 
    Do Until rst.EOF ' on parcourt les enregistrements de la table
 
        ' on récupère le recordset lié au champ multivalué de l'enregistrement courant
        Set rstMV = rst(nomChampMV).Value
 
        ' on parcourt les valeurs du champ multivalué
        Do Until rstMV.EOF
            
            ' ajout de la valeur à la nouvelle table avec l'identifiant de la table principale
            rst2.AddNew
            rst2.Fields(nomChampVL) = rstMV.Fields(0)
            rst2.Fields(nomChampID) = rst.Fields(nomChampID)
            rst2.Update
 
            rstMV.Delete ' suppression de la valeur contenue dans le champ multivalué
 
            rstMV.MoveNext  ' prochaine valeur
 
        Loop
 
        ' prochain enregistrement de la table principale
        rst.MoveNext
 
    Loop
 
    rst.Close ' ferme le recordset basé sur la table pour éviter les conflits sur la prochaine instruction
    Set tdf = dbs.TableDefs(nomTable) ' référence à la table contenant le champ multivalué
    'tdf.Fields.Delete (nomChampMV) ' suppression du champ multivalué de la table principale
    
    ' on renvoie Vrai indiquant que l'extraction s'est bien passée
    ExtraireValeurs = True
 
exit_ExtraireValeurs: ' gestion de la sortie de la fonction
    On Error Resume Next
    ' fermeture et libération des variables objet
    rst2.Close
 
    Set rstMV = Nothing
    Set rst2 = Nothing
    Set rst = Nothing
    Set tdf = Nothing
 
    Set dbs = Nothing
 
    Exit Function
 
err_ExtraireValeurs: ' si une erreur s'est produite
    
    ' on affiche le numéro et le message de l'erreur
    MsgBox "Erreur d'exécution " & Err.Number & vbNewLine & vbNewLine & Err.Description, vbExclamation
    Resume exit_ExtraireValeurs ' on va à l'étiquette exit_ExtraireValeurs pour sortir proprement de la fonction
 
End Function
 
Public Function TableExiste(ByVal nomTable As String) As Boolean
    On Error Resume Next
    TableExiste = (CurrentDb.TableDefs(nomTable).Name = nomTable)
End Function

Public Function testExtraction()

    ' appel de la fonction
    If ExtraireValeurs("T_Examen", "IdExamen", "Inscrits", "T_Inscription_Examen", "IdCandidat") Then ' si la normalisation s'est bien passée
        ' on affiche un message pour indiquer que la normalisation s'est bien passée
        MsgBox "Normalisation de la table réussie !", vbExclamation 
    Else ' sinon
        MsgBox "Problème lors de la normalisation de la table !", vbCritical ' on signale un problème
    End If
    
    ' rafraîchit le panneau de navigation pour afficher la nouvelle table
    Application.RefreshDatabaseWindow
    
End Function

Le code permet aussi de gérer une erreur éventuelle, en affichant sa description et en libérant les variables à la fin pour sortir proprement de la fonction.

IV. Implémentation de la fonction

Le code précédent est à copier dans un module standard, ensuite, pour exécuter la fonction d'extraction des valeurs, on doit lui transmettre certains arguments :

Exemples d'arguments passés à la fonction :

  1. nomTable : "T_Examen" ;
  2. nomChampID : "IdExamen" ;
  3. nomChampMV : "Inscrits" ;
  4. nomTable2 : "T_Inscription_Examen" ;
  5. nomTableVL : "IdCandidat".
 
Sélectionnez
    ' appel de la fonction
    If ExtraireValeurs("T_Examen", "IdExamen", "Inscrits", "T_Inscription_Examen", "IdCandidat") Then ' si la normalisation s'est bien passée
        ' on affiche un message pour indiquer que la normalisation s'est bien passée
        MsgBox "Normalisation de la table réussie !", vbExclamation
    Else ' sinon
        MsgBox "Problème lors de la normalisation de la table !", vbCritical ' on signale un problème
    End If
    
    ' rafraîchit le panneau de navigation pour afficher la nouvelle table
    Application.RefreshDatabaseWindow


Contenu de la table principale, après l'extraction des valeurs et la suppression du champ multivalué :

Aperçu de la table après extraction
Aperçu de la table après extraction


Création de la table T_Inscription_Examen et ajout pour chaque examen de l'identifiant du candidat :

Aperçu de la table T_Inscription_Examen
Aperçu de la table T_Inscription_Examen


Modèle obtenu après mise en relation des tables :

Modèle relationnel
Modèle relationnel

On peut bien sûr ajouter dans le code d'autres appels à la fonction pour normaliser plusieurs tables à la fois.

Dans ce cas, le code va créer une table destinée à enregistrer les valeurs pour chaque table contenant un champ multivalué.


Si on prend par exemple les tables de départ :

  • T_Examen(IdExamen, IntituleExamen, .., Inscrits)
  • T_Reunion(IdReunion, DateReunion, .., Inscrits)


On peut normaliser ces deux tables en effectuant deux appels successifs à la fonction :

 
Sélectionnez
ExtraireValeurs "T_Examen", "IdExamen", "Inscrits", "T_Inscription_Examen", "IdCandidat"
ExtraireValeurs "T_Reunion", "IdReunion", "Inscrits", "T_Inscription_Reunion", "IdContact"


Pour obtenir après extraction les tables suivantes :

  • T_Examen(IdExamen, IntituleExamen, ..) ;
  • T_Inscription_Examen(IdExamen, IdCandidat) ;
  • T_Reunion(IdReunion, DateReunion, ..) ;
  • T_Inscription_Reunion(IdReunion, IdContact).

L'opération étant irréversible, pensez à bien faire une copie de la base avant l'exécution du code.

V. Conclusion

Après avoir intégré dans son application la fonction d'extraction des valeurs, on pourra ensuite facilement normaliser les tables contenant des champs multivalués pour ainsi réaliser une base de données qui respecte un bon schéma relationnel. Il ne restera alors plus qu'à adapter les autres objets de l'application à ce nouveau modèle de données.

VI. Fichier à télécharger

La base de données exemplenormaliser-tables contient la table avec le champ multivalué et la fonction permettant d'extraire les valeurs.

Pour tester l'extraction, exécuter la fonction testExtraction contenue dans le module de l'application.

VII. Remerciements

Je tiens à remercier Jean-Philippe Ambrosino, Thierry Gasperment, Pierre Fauconnier et Fabien Le Bris de m'avoir conseillé pour la réalisation de cet article, ainsi que escartefigue pour sa relecture.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   


Dans le domaine des base de données relationnelles et du langage SQL on utilise les termes « colonne » et « ligne » au lieu de « champ » et « enregistrement ». Mais comme il en a été décidé autrement pour Access, et que les termes « champ » et « enregistrement » sont désormais familiers pour tous les utilisateurs du logiciel, on préfère continuer à les employer dans nos articles pour la rubrique Access.

Copyright © 2022 Denis Hulo. Aucune reproduction, même partielle, ne peut être faite de ce site ni 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. Droits de diffusion permanents accordés à Developpez LLC.