Normaliser les tables Access contenant des champs multivalués

Denis Hulo

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

Niveau requis : avancé.

Commentez cet article : Commentez

- Titre : Normaliser les tables Access contenant des champs multivalués

- Auteur : Denis Hulo

- Parution : 22 mars 2022

- Public visé :

- Licence : Copyright ® 2022 Denis Hulo. 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. Tables de départ

- II-A. T_Candidat

- II-B. T_Examen

- III. Fonction d'extraction des valeurs

- IV. Implémentation de la fonction

- V. Conclusion

- VI. Fichier à télécharger

- VII. Remerciements

I - Introduction

Les 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

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 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 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

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

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 :

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

Loop

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

Code complet

Module M_Normaliser_Tables

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".

' 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

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

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

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 :

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 exemple 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.

(1)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.