Nettoyer une base de données en VBA Access

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Corriger les informations de base de données en VBA

Nous poursuivons ici la construction de l'application des QCM grâce à Access et au code Visual Basic. Dans les précédentes formations, nous avions conçu le formulaire d'accueil proposant à l'utilisateur, par le biais d'une liste déroulante, l'ensemble des questionnaires pour l'évaluation. Puis, dans un deuxième temps, nous avions réussi à transmettre les informations interactives de l'utilisateur, à un autre formulaire. Ainsi, le QCM choisi pouvait se charger et l'évaluation pouvait débuter.


Mais avant d'aller plus loin pour notamment contrôler les réponses fournies par le candidat en temps réel, nous souhaitons réaliser un petit nettoyage de la base de données. Certains champs de table ne sont pas nécessaires et certaines informations de champs peuvent être simplifiées. Comme tous les questionnaires sont conçus sur la même structure de table, nous allons pouvoir réaliser un traitement récurrent automatisé et productif.

Base de données à nettoyer
Pour débuter les travaux de nettoyage, nous récupérons l'application Access de QCM que nous sommes en train de bâtir et que nous faisons évoluer pas à pas.

• Télécharger la base de données nettoyer-bdd-vba.accdb en cliquant sur son lien,
• L'ouvrir dans Access et cliquer sur le bouton Activer le contenu si nécessaire,

Nous pourrions nous contenter de développer un code Visual Basic dans un module, et lancer son exécution sur demande, pour traiter les nouvelles tables importées. Mais autant rendre le processus visuel. Nous allons créer un formulaire simple, doté d'un bouton. Ce dernier déclenchera le traitement récurrent, pour nettoyer chacune des tables présentes dans la base de données. Nous ferons en sorte que le code puisse s'exécuter aussi simplement, pour toute nouvelle table importée, en ignorant celles qui ont déjà été purgées.

• Cliquer sur l'onglet Créer en haut de la fenêtre Access pour activer son ruban,
• Dans la section Formulaires du ruban, cliquer sur le bouton Formulaire vierge,

Ainsi nous créons un formulaire dépendant d'aucune source de données afin de le personnaliser à notre guise.

• Cliquer sur le bouton Affichage du ruban Création,
• Dans la liste, choisir Mode Création,
• Puis, cliquer sur le bouton Feuilles de propriétés, toujours dans le ruban Création du formulaire,

La feuille de propriétés permet en effet de personnaliser les contrôles. Nous l'utiliserons pour associer un code VBA au bouton que nous ajouterons. Nous l'utiliserons aussi pour attribuer un nom explicite à ce dernier. Les objets de programmation se pilotent en effet par leur nom, pour accéder à leurs propriétés et méthodes.

• Enregistrer le formulaire (CTRL + S) sous le nom : purger_tables,
• Tracer un bouton sur le formulaire à l'aide de la section Contrôles du ruban Création,
• Fermer la boîte de dialogue de l'assistant qui se déclenche en cliquant sur Annuler,
• Activer l'onglet Autres de la feuille de propriétés du bouton Sélectionné,
• Remplacer l'information de sa propriété Nom par : nettoyer,
• Activer l'onglet Format de sa feuille de propriétés,
• Définir sa propriété Légende sur Nettoyer,

Nous venons de définir le nom du bouton pour le piloter de façon explicite par le code VBA, ainsi que sa légende, soit le texte qu'il affiche pour guider l'utilisateur. Il nous reste à lui attacher la procédure événementielle qui permettra de déclencher le code de nettoyage de la base, au clic.

• Activer l'onglet Evénement de la feuille de propriétés pour le bouton sélectionné,
• Cliquer sur le petit bouton de son événement Au clic,
• Dans la boîte de dialogue qui suit, choisir Générateur de code et valider,

Nous basculons dans l'éditeur de code Visual Basic Access, entre les bornes de la procédure nettoyer_Click(). Comme nous l'apprend la formation pour débuter la programmation en VBA Access, tout code saisi entre les bornes de cette procédure, se déclenchera lorsqu'un clic utilisateur interviendra sur le bouton.

Réglages de l'éditeur de code Visual Basic
Avant de développer ce code, deux paramétrages sont nécessaires.

• En haut de la feuille de code, sous l'instruction Option Compare Database, ajouter la déclarative suivante :

Option Explicit

Cette instruction rend VBA Access moins permissif. Toutes les variables sont contrôlées. Il est désormais impossible d'utiliser des noms de variables mal orthographiées ou simplement pas déclarées. C'est une sécurité qui impose de bien typer chacune des variables à utiliser dans le code pour optimiser les ressources notamment .

• Cliquer sur le menu Outils en haut de la fenêtre Access,
• Dans la liste, choisir Références,
• Dans la boîte de dialogue qui suit, cocher la référence Microsoft ActiveX Data Objects 6.1 Library, puis valider par Ok,

Nous en avons l'habitude désormais, pour accéder aux informations de la base de données et les manipuler, nous avons besoin d'ajouter la référence à ADO (ActiveX Data Objects). Cette librairie permet d'instancier des classes qui par héritage, permettent de créer des objets VBA de programmation pour accéder aux données.

Inspection de la base de données
Avant de développer le code destiné à nettoyer les tables de questionnaire de la base de données, nous allons passer en revue les modifications à réaliser.

• Réaliser le raccourci ALT + F11 pour basculer sur l'application Access,
• Double cliquer sur la première table Adobe Photoshop depuis le volet des objets Access,

La table propose 36 enregistrements, soit 36 questions pour le QCM. Pour chaque question du champ QUESTION, sont proposés quatre choix dans leur champ respectif. Le champ REPONSES offre le choix correct à formuler par l'utilisateur pour cumuler les points. Pour chaque enregistrement de ce champ, nous ne souhaitons conserver que le numéro de la bonne réponse. Le préfixe choix doit donc être supprimé. Pour chacune des tables, nous déclencherons donc en VBA, une requête SQL de mise à jour (Update) des données sur ce champ. Les champs Type, COEFF et QuestionPassée ne sont pas utiles. Nous les supprimerons donc à l'aide des objets de base de données et de leurs propriétés et méthodes. Il sera nécessaire de parcourir l'ensemble des champs pur chaque table, à l'aide d'une boucle de traitement.



Pour ces opérations de manipulation de tables, nous aurons besoin de déclarer des objets permettant de manipuler les différents éléments de la base de données. C'est ce que nous allons faire, sans plus attendre.

• Fermer la table et revenir dans l'éditeur de code par le raccourci ALT + F11,

Mise à jour de table par le code Visual Basic Access
Comme toujours nous commençons par déclarer toutes les variables dont nous aurons besoin.

• Entre les bornes de la procédure nettoyer_Click, ajouter les déclarations suivantes :

Dim la_base As Database: Dim champ As Field : Dim chaque_table As TableDef
Dim fichier As Object
Dim nom_copie As String : Dim requete As String
Dim i As Byte

Pour accéder aux objets de la base de données, nous avons tout d'abord besoin d'un objet permettant de manipuler cette dernière. C'est pourquoi nous déclarons la variable la_base que nous ferons pointer sur la base de données en cours. Nous déclarons ensuite la variable champ comme un objet ADO de type Field qui permettra de parcourir tous les champs de chaque table, à la recherche de ceux à supprimer. De la même façon nous déclarons la variable chaque_table comme un objet ADO de type TableDef afin de pouvoir accéder aux tables de la base de données, pour les parcourir toutes. Nous utiliserons la variable fichier, déclarée comme un objet, pour instancier un objet permettant d'accéder aux fichiers et dossiers de l'ordinateur, afin de réaliser une copie compactée de la base de données, à l'issue du traitement. Les variables nom_copie et requete sont déclarées comme des string pour stocker des informations sous forme de chaîne de caractères. La première servira à stocker le chemin et le nom de la copie de la base de données à compacter. La seconde permettra de mémoriser la syntaxe complète de la requête SQL de mise à jour sur le champ REPONSES.

Après les déclarations suivent les affectations, pour les variables qui n'attendent pas des données issues du traitement par le code.

• A la suite de la procédure, ajouter les affectations suivantes :

Set la_base = Application.CurrentDb
Set fichier = CreateObject("scripting.FilesystemObject")
nom_copie = Application.CurrentProject.Path & "\safe.accdb"

La méthode CurrentDb de l'objet Application permet de faire pointer l'objet DataBase sur la base de données en cours. Ainsi nous pourrons accéder aux propriétés et méthodes qui permettront de manipuler les différents objets de la base de données, comme les tables ou encore les champs de tables. Nous en avons l'habitude désormais, la fonction VBA CreateObject permet d'instancier un objet ActiveX. Avec l'argument scripting.FilesystemObject, nous initialisons un objet qui sera capable d'accéder aux fichiers et dossiers de l'ordinateur. Enfin, dans la variable String nom_copie, nous mémorisons le chemin complet d'accès au nom du fichier que nous utiliserons pour réaliser une copie compactée de la base de données. La propriété CurrentProject de l'objet VBA Application permet d'accéder à la propriété dérivée Path qui restitue le chemin complet au dossier de l'application Access en cours. A ce chemin, nous concaténons (& "\safe.accdb") le nom que nous souhaitons attribuer à la copie.

Nous devons maintenant accéder aux tables de la base de données grâce à notre objet chaque_table. Nous allons répliquer le code que nous avions développé dans la formation permettant de charger le contenu d'une liste déroulante des noms de tables. Ce type de traitement récurrent permettant de parcourir tous les éléments d'un groupe d'objets se réalise à l'aide d'une boucle For Each.

• A la suite du code, ajouter les instructions suivantes :

For Each chaque_table In la_base.TableDefs
If UCase(Left(chaque_table.Name, 3)) <> "MSY" And Left(chaque_table.Name, 1) <> "~" And chaque_table.Name <> "ListeTables" Then

End If
Next chaque_table

Nous parcourons toutes les tables (For Each chaque_table) présentes dans la base de données en cours (In la_base.TableDefs). Comme nous l'avions vu dans la formation pour charger la liste déroulante, nous ignorons les tables système (MSY), temporaires (~) ainsi que la table ListeTables qui est la source de données de la liste déroulante du formulaire Listes.

Pour contrôler notre code à chaque étape, nous allons exploiter la fonction VBA MsgBox, afin de voir si nous sommes capables de récupérer tous les noms de table, en parcourant la base de données active.

• Avant le End If de la boucle, ajouter l'instruction de code suivante :

MsgBox chaque_table.Name

La propriété Name d'un objet de type Table retourne le nom de la table pointée.

• Enregistrer les modifications (CTRL + S) et basculer sur l'application Access (ALT + F11),
• Double cliquer sur le formulaire purger_tables depuis le volet Access pour l'exécuter,
• Cliquer sur le bouton Nettoyer du formulaire pour déclencher l'exécution du code,

Comme vous le remarquez, le nom de la première table s'affiche dans une boîte de dialogue générée par la fonction VBA MsgBox. Nous devons valider par Ok chacun de ces messages. Comme nous sommes dans une boucle de traitement, la boîte de dialogue s'affiche autant de fois qu'il y a de tables correspondant aux critères de l'instruction conditionnelle If écrite en VBA.



Supprimer des champs de table en VBA Access
Puisque nous arrivons à accéder à chacune des tables de la base de données, nous devons maintenant tenter d'accéder aux champs de chacune de ces tables. Certains doivent être supprimés et d'autres doivent être mis à jour.

• Fermer le formulaire et revenir dans l'éditeur de code VBA (ALT + F11),
• Supprimer la ligne de test du MsgBox,
• A la place, ajouter les bornes de la boucle permettant de parcourir tous les champs de la table en cours :

For i = 0 To chaque_table.Fields.Count - 1

Next i

Un objet de type table propose une propriété Fields qui est une collection représentant tous les champs de la table désignée par l'objet en question. En découle une propriété Count qui retourne le nombre de champs contenus dans la table. Ainsi, en tenant compte du fait que nous partons de l'indice 0, nous parcourons tous les champs jusqu'à l'indice repéré par le nombre de champs moins un. Nous aurions pu utiliser une boucle For Each pour parcourir tous ces champs. Pour cela nous aurions dû déclarer un objet de type Field et parcourir tous les éléments du groupe chaque_table.Fields. Mais comme l'objectif ici est de supprimer des champs, nous en aurions sauté certains. Alors qu'une boucle classique For Next, nous permet de décrémenter le compteur lorsque c'est nécessaire, afin de pointer sur le bon champ quand le précédent est supprimé. C'est une technique que nous avions mise en oeuvre pour nettoyer les données de tableaux Excel en supprimant certaines lignes.

La suppression des champs de table se fait grâce à une méthode d'un objet table, en désignant en paramètre le nom du champ. Il faut donc vérifier le champ pointé par la boucle en cours de lecture, avant d'initier la suppression :

• Dans la boucle For Next, ajouter le code de suppression de champ suivant :

If (chaque_table.Fields(i).Name = "Type" Or chaque_table.Fields(i).Name = "COEFF") Then
chaque_table.Fields.Delete chaque_table.Fields(i).Name
i = i - 1
End If

If (chaque_table.Fields(i).Name = "QuestionPassée") Then
chaque_table.Fields.Delete chaque_table.Fields(i).Name
Exit For
End If

La première instruction de branchement permet de réaliser un premier test non exclusif sur le nom des champs. S'il s'agit de l'un des deux premiers, la méthode Delete de la propriété Fields de l'objet de type table, permet de supprimer le champ dont le nom est passé en paramètre. Pour cela, la propriété Fields permet de pointer sur l'un des champs de la table, grâce à sa position, repérée par l'indice i de lecture dans la boucle. A chaque suppression qui place le pointeur sur le champ suivant, nous décrémentons la variable i, pour que la boucle n'oublie pas de revenir sur le champ suivant et le tester. Le test sur le dernier champ à supprimer est volontairement isolé. Car une fois ce dernier champ supprimé, l'exécution de la boucle doit être stoppée (Exit For). Comme nous supprimons des champs au fur et à mesure, la valeur renvoyée par la propriété Count sur le nombre de champ n'est plus valide. Le code, en allant trop loin, génèrerait une erreur.

Nous allons tester le programme pour nous assurer que les instructions codées, permettent bien de supprimer les champs désignés et ce, pour chacune des tables de la base de données.

• Enregistrer les modifications (CTRL + S) et basculer sur l'application Access (ALT + F11),
• Double cliquer sur le formulaire purger_tables afin de l'exécuter,
• Cliquer sur le bouton Nettoyer afin de déclencher le code VBA,
• Ouvrir plusieurs tables pour constater les modifications,

Comme vous le constatez, grâce aux boucles imbriquées permettant de parcourir toutes les tables et pour chacune, tous leurs champs, le code VBA a bien réussi à les supprimer. Comme cette suppression est conditionnelle, bien qu'ils n'existent plus désormais, le code peut être exécuté de nouveau, sans générer d'erreur.

• Fermer les tables ouvertes ainsi que le formulaire,
• Revenir dans l'éditeur de code Visual Basic (ALT + F11),

Modifier les enregistrements en VBA par requête SQL
Nous avions déjà appris à attaquer les données d'une base en injectant des instructions SQL dans du code Visual Basic Access. Mais il s'agissait seulement de réaliser des requêtes sélection sur une table de la base de données. Ici nous souhaitons mettre à jour les informations d'un champ, pour tous les enregistrements de toutes les tables. Donc la chaîne SQL doit être construite à l'intérieur de la boucle For Each qui parcourt chacune des tables, afin de lui passer en variable, le nom de la table sur laquelle opérer la mise à jour. Dans la syntaxe SQL, le mot clé utilisé pour la mise à jour est UPDATE, et non plus SELECT que nous utilisions pour filtrer les enregistrements selon critères (Clause WHERE). Dans le champ REPONSES, il s'agit de supprimer le préfixe choix, afin de ne conserver que le numéro de la bonne réponse à la question posée.

• Après le Next i de la boucle parcourant les champs et avant le End If de la condition sur les noms de tables, ajouter les deux lignes de code suivantes :

requete = "UPDATE [" & chaque_table.Name & "] SET REPONSES = REPLACE (REPONSES , 'choix ' ,'' )"
la_base.Execute requete

Nous stockons l'expression SQL dans la variable requete que nous avions déclarée en tant que String à cet effet. Le mot clé UPDATE est toujours suivi du nom de la table sur laquelle doit s'opérer la modification. Comme certains noms de tables possèdent des espaces, nous l'inscrivons entre crochets pour qu'il soit parfaitement délimité dans la syntaxe SQL. Comme le nom de la table n'est connu qu'au moment de sa lecture dans la boucle, nous concaténons (&) l'instruction avec l'information retournée par la propriété Name de l'objet Table. Le mot clé SET permet de désigner le champ sur lequel la mise à jour doit être réalisée. La fonction REPLACE en SQL permet de remplacer une occurrence de chaîne de caractères par une autre. Dans le champ REPONSES, nous remplaçons le terme choix suivi d'un espace, par une chaîne de caractères vides (''). En d'autres termes, nous le supprimons. Puis nous affectons ce résultat au champ REPONSES de la table (SET REPONSES = REPLACE (...)). C'est enfin la méthode Execute de l'objet de base de données qui permet de déclencher la requête action désignée en paramètre.

Avant d'aller plus loin, une fois encore, nous allons tester la fonctionnalité du code.

• Enregistrer les modifications (CTRL + S) et basculer sur l'application Access (ALT + F11),
• Double cliquer sur le formulaire purger_tables,
• Cliquer sur le bouton Nettoyer pour commander l'exécution du code VBA,
• Ouvrir plusieurs tables pour constater les modifications induites,

Comme vous le remarquez, la mise à jour de tous les enregistrements sur le champ REPONSES pour toutes les tables a parfaitement fonctionné. Comme l'illustre la capture ci-dessous, l'information textuelle choix a disparu pour ne conserver que le chiffre correspondant à la bonne réponse.

Il est intéressant de noter que nous avons donc appris à réaliser des mises à jour de masse, par le code VBA déclenchant l'exécution d'une requête action, sur toutes les tables de la base, grâce à une boucle capable de parcourir tous les objets de la base de données. De plus, le code est relativement simple, pour un traitement récurrent on ne peut plus productif.

Comme des champs ont été supprimés sur toutes les tables de questionnaire et comme les informations du champ REPONSES ont été simplifiées, la base de données est susceptible d'être moins volumineuse. Cependant un gestionnaire de base de données écrit en temps réel sur le disque dur. Donc cette perte de volume ne se traduit pas directement par une diminution de la taille du fichier.

• Fermer toutes les tables ainsi que le formulaire,
• Revenir dans l'éditeur de code VBA Access (ALT + F11),

Compacter une base de données mise à jour
Pour que l'incidence des modifications réalisées par le code puisse s'observer sur la taille du fichier, nous devons compacter la base de données. Mais pour être compactée, la base de données ne peut pas être ouverte. Donc nous allons tout d'abord réaliser une copie de cette base, grâce à la variable objet fichier que nous avions déclarée à cet effet. Puis nous compacterons cette copie. Le compactage régulier d'une base de données permet en outre d'optimiser les performances et temps de réponse.

• Après la boucle (Next chaque_table) et avant le End Sub, ajouter les instructions suivantes :

fichier.copyfile Application.CurrentDb.Name, Application.CurrentDb.Name & ".SRO", True
DBEngine.CompactDatabase Application.CurrentDb.Name & ".SRO", nom_copie
fichier.DeleteFile Application.CurrentDb.Name & ".SRO"

La méthode copyfile de notre objet ActiveX permet de réaliser une copie de la base de données en désignant, le fichier source et le fichier destination. Nous utilisons le même chemin et le même nom pour réaliser la copie, en ajoutant une extension supplémentaire ( & ".SRO" ). Le dernier paramètre de la méthode copyfile, réglé à true, indique à VBA d'écraser le fichier de destination s'il existe déjà. Ensuite la méthode CompactDatabase de l'objet VBA DBEngine permet de compacter la copie désignée en paramètre pour produire à l'issue un nouveau fichier, renseigné en deuxième argument, grâce à la variable nom_copie. Enfin, grâce à la méthode DeleteFile de l'objet ActiveX fichier pour manipuler les fichiers et dossiers, nous supprimons la copie temporaire qui a servi au compactage de la base de données.

Le code VBA est terminé. Mais avant de le tester, nous devons libérer les ressources des objets chargés en mémoire. Tout d'abord, toute connexion ouverte à une base de données, doit être fermée. Ensuite les variables objets doivent être déchargées (Nothing) pour libérer proprement les ressources.

• A la suite du code, ajouter les lignes VBA suivantes :

la_base.Close

Set chaque_table = Nothing
Set champ = Nothing
Set la_base = Nothing

La méthode Close d'un objet de base de données permet de fermer proprement la connexion établie. Ensuite, grâce aux mots clés Nothing et Set nous déchargeons les variables objets pour libérer la mémoire des ressources qu'elles exploitent.

• Enregistrer les modifications (CTRL + S) et basculer sur l'application Access (ALT + F11),
• Double cliquer sur le formulaire purger_tables pour l'exécuter,
• Cliquer sur le bouton Nettoyer pour déclencher le code VBA,

En apparence rien ne se produit, d'autant que les tables ont toutes déjà été nettoyées par les essais précédents. En réalité, le code a bien réalisé une copie compactée de la base de données purgée.

• Ouvrir le dossier de la base de données dans l'explorateur Windows,

La base de données safe.accdb a bien été créée. Comme vous le constatez, sa taille est quasiment deux fois inférieure à celle de l'originale. De plus, ses performances s'en trouvent accrues.

Maintenant que les tables des questionnaires de la base de données ont été nettoyées, dans une prochaine formation, nous pourrons reprendre le développement de l'application Access pour évaluer les candidats par des QCM. Il s'agira notamment de permettre à un utilisateur de s'identifier pour que ses résultats puissent être mémorisés et archivés dans une table liée. Le code complet qui a permis de purger les tables de la base de données, est le suivant :

Private Sub nettoyer_Click()
Dim la_base As Database: Dim champ As Field
Dim fichier As Object
Dim nom_copie As String: Dim requete As String
Dim chaque_table As TableDef
Dim i As Byte

Set la_base = Application.CurrentDb
Set fichier = CreateObject("scripting.FilesystemObject")
nom_copie = Application.CurrentProject.Path & "\safe.accdb"

For Each chaque_table In la_base.TableDefs
If UCase(Left(chaque_table.Name, 3)) <> "MSY" And Left(chaque_table.Name, 1) <> "~" And chaque_table.Name <> "ListeTables" Then
For i = 0 To chaque_table.Fields.Count - 1
If (chaque_table.Fields(i).Name = "Type" Or chaque_table.Fields(i).Name = "COEFF") Then
chaque_table.Fields.Delete chaque_table.Fields(i).Name
i = i - 1
End If

If (chaque_table.Fields(i).Name = "QuestionPassée") Then
chaque_table.Fields.Delete chaque_table.Fields(i).Name
Exit For
End If
Next i

requete = "UPDATE [" & chaque_table.Name & "] SET REPONSES = REPLACE (REPONSES , 'choix ' , '' )"
la_base.Execute requete
End If
Next chaque_table

fichier.copyfile Application.CurrentDb.Name, Application.CurrentDb.Name & ".SRO", True
DBEngine.CompactDatabase Application.CurrentDb.Name & ".SRO", nom_copie
fichier.DeleteFile Application.CurrentDb.Name & ".SRO"

la_base.Close

Set chaque_table = Nothing
Set champ = Nothing
Set la_base = Nothing

End Sub