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 :
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 :
Aperçu de quelques données avant l'extraction :
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 :
- nomTable : nom de la table contenant la colonne multivaluée ;
- nomChampID : nom du champ identifiant de la table ;
- nomChampMV : nom du champ multivalué ;
- nomTable2 : nom de la table contenant les valeurs extraites ;
- 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 :
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é :
' 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 :
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
LoopOn suppose pour simplifier que la table ne contient qu'un seul champ multivalué.
Code complet
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 FunctionLe 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 :
- nomTable : "T_Examen" ;
- nomChampID : "IdExamen" ;
- nomChampMV : "Inscrits" ;
- nomTable2 : "T_Inscription_Examen" ;
- nomTableVL : "IdCandidat".
' 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é :
Création de la table T_Inscription_Examen et ajout pour chaque examen de l'identifiant du candidat :
Modèle obtenu après mise en relation des tables :
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 :
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.