Corriger et exporter les données d'une base en VBA
Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :
Nettoyer et exporter les données en Visual Basic Access
Dans cette formation VBA Access, nous proposons de réaliser l'exportation des données après nettoyage, en vue d'une exploitation par un logiciel tiers, comme Excel. Cette base des idées de sorties offre des miniatures d'images, archivées dans un sous dossier de l'application. Mais toutes n'existent pas. Certains enregistrements sont donc orphelins. En parcourant le sous dossier des miniatures, nous devons donc tester leur existence. Si elle n'est pas présente, l'enregistrement correspondant doit être supprimé. Nous devons de même purger le champ Description des balises Html prévues pour une restitution Web, afin de permettre une exploitation textuelle correcte. Puis, nous exporterons le résultat au format universel CSV pour permettre des importations et extractions, notamment avec VBA Excel, afin de traiter et manipuler les photos. Comme la base de données aura été purgée, nous proposerons enfin de la compacter par le code.
La capture ci-dessus propose un extrait des données de la base Access à partir de laquelle nous proposons de travailler. Le volume de cette table a volontairement été restreint pour les besoins du téléchargement. Elle offre néanmoins plus de 500 enregistrements. Chacun de ces enregistrements est décrit sur 7 Champs. L'un d'entre eux, le champ societes_descriptif est particulièrement riche, puisqu'il propose une description textuelle multiligne de chaque activité recensée.
Sources et présentation de la problématique
Pour débuter directement nos travaux par le code VBA Access, il est donc nécessaire de disposer des données source.
- Télécharger le fichier nettoyer-exporter.rar en cliquant sur son lien,
- Puis, le décompresser dans le dossier de votre choix,
- Double cliquer sur le sous dossier images pour afficher son contenu dans l'explorateur,
Comme l'indique la barre d'état en bas de la fenêtre de l'explorateur Windows, ce dossier ne contient que 273 éléments, soit 273 images. Or la table de la base de données, compte près de 600 enregistrements. Cela signifie que 300 lignes environ, sont orphelines. Il s'agit de les détecter pour les supprimer. Et pour ce faire, nous devons parcourir chaque enregistrement de la base de données. Et pour chacun d'entre eux, nous devons vérifier la présence de l'image spécifiée dans le champ societes_photo1. Nous allons donc devoir, pour chaque enregistrement, parcourir l'ensemble des fichiers contenus dans le sous dossier images, à la recherche d'une correspondance.
- A la racine du dossier de décompression, double cliquer sur le fichier nettoyer-exporter.mdb, pour l'ouvrir dans Access,
- Puis, cliquer sur le bouton Activer le contenu du bandeau de sécurité,
- Dans le volet des objets Access sur la gauche, double cliquer sur la table id_sorties pour afficher son contenu,
Enfin, vous avez pu constater l'extension ancienne génération de cette base de données(.mdb). Cette base est en effet le fruit d'une exportation d'un site Web professionnel. Et ce traitement a été réalisé il y a quelques années. Mais ce format n'a aucune incidence. Nous pourrons exploiter tous les objets de programmation VBA Access, comme si nous le faisions avec une base de données à l'indice actuel, d'extension accdb.
Parcourir les enregistrements par le code VBA
Comme un formulaire n'est pas nécessaire pour cette application dédiée au traitement, nous proposons de créer un module. Ce module est destiné à accueillir nos procédures de code VBA.
- Fermer la table id_sorties en cliquant sur sa croix située en haut à droite de sa fenêtre,
- Cliquer sur l'onglet Créer en haut de la fenêtre Access pour activer son ruban,
- Tout à fait à droite du ruban, dans la section Macros et code, cliquer sur le bouton Module,
- Réaliser le raccourci clavier CTRL + S,
- Dans la petite boîte de dialogue qui surgit, saisir le nom : purger et valider par Ok,
- Dans sa feuille de code, au centre de l'écran, sous l'instruction Option Compare Database, ajouter l'instruction suivante :
Comme nous l'avons évoqué à maintes reprises dans nos formations VBA Access, cette instruction impose à l'éditeur d'effectuer le contrôle de chacune des variables. De fait, il sera interdit de réaliser des erreurs dans leur désignation ou encore d'employer des variables non déclarées. Nous codons au plus juste.
- A la suite dans la feuille de code, créer les bornes de la procédure nettoyer_exporter :
End Sub
Il s'agit de la procédure principale destinée aux traitements récursifs pour parcourir à la fois, les enregistrements de la base de données et les fichiers du sous dossier images.
Comme toujours, avant de débuter le codage, nous devons commencer par déclarer les variables nécessaires au traitement.
- Dans les bornes de la procédure, ajouter les déclarations suivantes :
Dim nom_dossier As String : Dim nom_fichier As String
Dim lid As String : Dim desc As String
Dim ligne_export As String : Dim requete As String
Dim fichier As Object
Nous commençons par déclarer les deux objets nécessaires pour accéder aux éléments de base de données. L'objet ligne typé comme un Recordset est prévu pour manipuler les enregistrements résultant des requêtes exécutées sur la base. Et précisément, l'objet base est destiné à manipuler la base de données en cours. Mais encore faut il initialiser ces objets afin qu'ils instancient les classes proposant ces propriétés et méthodes. Ces déclarations ont été rendues possibles grâce à l'ajout de la référence ActiveX Data Object.
C'est ce qu'illustre la boîte de dialogue dans l'extrait de la capture ci-dessus. Vous pouvez l'atteindre en déroulant le menu Outils de l'éditeur, puis en cliquant sur la rubrique Références. Si elle n'est pas cochée, vous devez l'ajouter avec le numéro (6.1 ici) le plus élevé, soit le dernier indice.
Les variables nom_dossier et nom_fichier déclarées comme des String, sont prévues pour mémoriser les chemins d'accès complets, respectivement au sous dossier images et à chacun des fichiers qu'il contient.
Ensuite les variables lid et desc permettront de prélever les informations des champs societes_id et societes_descriptif, pour traitement. La variable ligne_export, elle aussi typée comme un String, doit servir à reconstituer les informations concaténées pour chaque enregistrement, afin de les exporter dans un fichier Csv. La variable requete servira à mémoriser la syntaxe Sql des requêtes actions à exécuter sur la table. Enfin la variable fichier est déclarée comme une variable de type Object. Son objectif est en effet d'instancier la classe lui permettant d'hériter des méthodes nécessaires pour parcourir les fichiers d'un dossier.
Fort logiquement, nous proposons désormais d'affecter les premières variables.
- A la suite du code, ajouter les affectations suivantes :
Set base = Application.CurrentDb
Set ligne = base.OpenRecordset("id_sorties", dbOpenTable, dbReadOnly)
Set fichier = CreateObject("scripting.filesystemobject")
Tout d'abord, la propriété CurrentProject de l'objet VBA Access Application permet de désigner le projet en cours. Son attribut Path retourne alors le chemin d'accès complet au dossier de la base de données. C'est la raison pour laquelle nous le concaténons avec le nom du sous dossier (& "\images\"). Ainsi nous mémorisons bien le chemin d'accès au sous dossier images, dans la variable nom_dossier.
Les trois affectations qui suivent consistent à instancier des classes. Et dans ce cas, le mot clé Set est nécessaire. Grâce à la méthode CurrentDb de l'objet Application, nous désignons la base de données en cours. De fait, l'objet base hérite des pouvoirs pour la manipuler. D'ailleurs nous exploitons dans la foulée sa méthode OpenRecordset. Comme nous l'avait appris la formation VBA Access pour accéder aux données, cette méthode requiert trois arguments. Le premier peut être une requête Sql ou une table. Ici, nous lui passons la table id_sorties afin d'accéder à ses enregistrements. Puis, grâce aux deux paramètres qui suivent, enseignés par l'aide en ligne, nous définissons le type d'accès (dbOpenTable) et le mode (dbReadOnly), soit en lecture seule. Il s'agit en effet de prélever et non d'écrire. Nous préservons ainsi l'intégrité des données. De fait, notre objet ligne hérite des méthodes permettant de manipuler les enregistrements de cette table.
Enfin nous initialisons la dernière variable objet grâce à la fonction VBA CreateObject. Comme nous l'avait appris la formation VBA Access pour archiver les données, elle permet d'instancier une classe selon le paramètre qui lui est passé. Et ce paramètre scripting.filesystemobject permet à la variable fichier d'hériter de tous les pouvoirs, pour manipuler les fichiers du disque dur. C'est grâce à elle que nous pourrons atteindre les miniatures du sous dossier images, par le code VBA.
- A la suite du code, ajouter les instructions suivantes :
Close #1
Lorsque nous parcourrons les enregistrements, nous aurons besoin de les exporter, après les avoir nettoyés. Pour pouvoir les exporter en ajoutant les nouvelles données à la suite des précédentes, le fichier externe doit exister. Ce bloc VBA permet de créer le fichier exportation.txt dans le dossier de l'application. En effet, comme nous l'avait appris la formation VBA Excel pour importer et exporter les données, l'instruction VBA Open permet d'accéder en mémoire au fichier désigné juste après. Le mode For Output spécifie un accès en écriture avec écrasement. Si le fichier existe déjà , il est remplacé et donc réinitialisé.
Il est temps d'accéder aux enregistrements de la table id_sorties. Et pour cela, nous devons exploiter l'objet Recordset que nous avons ainsi initialisé.
- A la suite du code, ajouter les deux instructions suivantes :
ligne.MoveFirst
Tout d'abord, la propriété RecordCount d'un objet Recordset permet de renseigner sur le nombre d'enregistrements présents dans la sélection définie. Nous testons donc s'ils existent. Le cas échéant, nous stoppons l'exécution du code. Dans le cas où les enregistrements sont bien trouvés, nous plaçons le pointeur de lecture sur le premier d'entre eux, grâce à la méthode MoveFirst de l'objet Recordset. Dès lors, nous devons initialiser une boucle permettant de les parcourir tous.
- A la suite du code, ajouter les instructions VBA suivantes :
ligne.MoveNext
Loop Until ligne.EOF = True
Une boucle VBA Do Loop permet de réaliser un traitement récursif tant qu'un critère à valider n'est pas vérifié. C'est l'instruction Until qui permet d'annoncer ce critère. La propriété EOF de l'objet Recordset renvoie un booléen déterminant si la fin des enregistrements est atteinte. Donc, tant que ce booléen ne vaut pas True, le traitement de la boucle se poursuit.
Supprimer des enregistrements d'une base de données
Pour chaque enregistrement passé en revue dans la boucle, nous devons prélever le nom du fichier image associé. Nous devons alors tester l'existence de ce fichier dans le sous dossier images, grâce à notre objet fichier, instancié en début de code. Si la miniature n'est pas trouvée, nous devons supprimer l'enregistrement correspondant. Et pour ce faire, nous exécuterons une requête SQL de suppression sur la table id_sorties.
- Dans les bornes de la boucle, avant l'instruction ligne.MoveNext, ajouter les lignes de code suivantes :
lid = ligne.Fields("societes_id").Value
If fichier.FileExists(nom_fichier) = False Then
requete = "DELETE FROM id_sorties WHERE societes_id =" & lid
base.Execute requete
Else
End If
Tout d'abord, nous récupérons les informations nécessaires. La propriété Fields de l'objet Recordset permet d'atteindre le champ qui lui est passé en paramètre. Sa propriété Value accède alors au contenu du champ societes_photo1, pour l'enregistrement en cours de lecture. Le nom de l'image ainsi récolté est concaténé au chemin d'accès complet, que nous avions mémorisé dans la variable nom_dossier. Le tout est stocké dans la variable nom_fichier qui permettra le test de comparaison. De la même façon, nous récupérons l'identifiant de l'enregistrement en cours, dans la variable lid. Le champ societes_id est le champ de la clé primaire. Cette information permettra de désigner l'enregistrement à supprimer, si l'image n'est pas trouvée, grâce à une clause Where dans la requête SQL.
Ensuite et comme nous l'avions dit au moment de l'affectation, l'objet fichier a hérité, grâce à la fonction VBA CreateObject, des propriétés et méthodes permettant de manipuler les fichiers du disque. Ainsi, sa propriété FileExists retourne un booléen permettant de savoir si le fichier qui lui est passé en paramètre existe ou non. C'est la raison pour laquelle nous intégrons le test dans le critère d'une instruction conditionnelle If. Lorsque le critère est vérifié, le booléen retourné est False, signifiant que le fichier correspondant est absent. Dans ce cas, nous devons supprimer l'enregistrement. Nous écrivons donc la syntaxe de la requête de suppression, dans la variable requete. C'est le mot clé Delete en SQL qui initie une requête suppression. Dans la syntaxe, nous lui indiquons d'agir sur la table id_sorties (FROM id_sorties), mais seulement pour l'enregistrement en cours (WHERE societes_id=" & lid). Comme lid est une variable, elle doit être concaténée (&) à la chaîne statique. Enfin, c'est la méthode Execute de l'objet Database qui ordonne l'exécution de la requête action qui lui est passée en paramètre.
Nettoyer des champs par les expressions régulières
Dans le cas où le fichier image est bien trouvé au contraire (Else), il s'agit de traiter l'information du champ societes_descriptif, pour le purger de ses balises Html. Après nettoyage, nous pourrons mettre à jour les données purgées dans l'enregistrement en cours, toujours grâce à l'exécution d'une requête Sql. Pour cela, nous devons commencer par récolter les informations du champ, pour l'enregistrement en cours de lecture.
- Dans la branche Else de l'instruction If, ajouter les deux lignes suivantes :
desc = purger_balises(desc)
Comme précédemment, la propriété Fields de l'objet Recordset permet d'atteindre le champ societes_descriptif. Grâce à sa propriété dérivée Value, nous stockons son contenu dans la variable desc, pour traitement. Et d'ailleurs dans la foulée, nous appelons la fonction purger_balises. Cette dernière n'existe pas. Nous devons la créer. Sa mission est de purger le contenu de la variable qui lui est passée en paramètre, des balises Html et de retourner le résultat après traitement. Ainsi, nous réceptionnerons le contenu du champ sans les balises, dans la variable desc. Il ne restera plus qu'à mettre à jour l'enregistrement en cours, avec ces nouvelles données. Nous devons donc quitter temporairement le développement de la procédure nettoyer_exporter.
- Après le End Sub de la procédure nettoyer_exporter, créer la fonction purger_balises, comme suit :
Dim expReg As Object : Dim motif As Object
End Function
Le but d'une fonction est de retourner une valeur après traitement. C'est le mot clé Function qui permet de la déclarer. Cette fonction attend le texte du champ societes_descriptif, mémorisé dans la variable desc, pour le nettoyer. Nous déclarons donc la variable chaine comme un String, pour que ce paramètre puisse lui être passé. Après traitement, la fonction retourne elle-même une chaîne de caractères. Donc nous la typons aussi comme un String. Puis, nous déclarons les deux variables nécessaires pour piloter les expressions régulières. La variable expReg doit instancier la classe de ces expressions pour hériter des propriétés et méthodes. La variable motif doit permettre de vérifier la présence d'occurrences présentant une forme remarquable, celle des balises Html, afin de les supprimer.
Mais avant de purger toutes les balises, nous souhaitons remplacer celles qui consistent en un saut de ligne par un tiret, afin de conserver les séparations explicites, après nettoyage. Et pour ce faire, nous pouvons exploiter la fonction VBA Replace qui permet de remplacer une occurrence par une autre, dans une chaîne de caractères.
- Après les déclarations de la fonction, ajouter les instructions VBA suivantes :
chaine = Replace(chaine, "</BR>", "-")
chaine = Replace(chaine, "</P>", "-")
chaine = Replace(chaine, " ", " ")
Les balises BR et /P provoquent un saut de ligne. Nous les remplaçons donc par le tiret et stockons le résultat dans la variable d'origine, la variable chaine. Le code transcrit un espace en langage Html. Mais comme il ne peut être interprété que par un navigateur Web, nous le remplaçons par un véritable espace.
Puisque les éléments que nous souhaitions conserver sont enregistrés dans la variable chaine sous une autre forme, nous pouvons désormais enclencher le traitement permettant de purger les balises Html. Nous devons dans un premier temps instancier la classe permettant d'hériter des pouvoirs pour manipuler les expressions régulières.
- A la suite de la fonction, ajouter les trois lignes de code VBA suivantes :
expReg.Pattern = "(<)[0-9a-zA-Z_ ?~#\/.:;'=-]*(>)"
Set motif = expReg.Execute(chaine)
Comme toujours, pour initialiser une variable objet, le mot clé Set est nécessaire. C'est ensuite et encore une fois la fonction VBA CreateObject qui permet d'instancier une classe, selon le paramètre qui lui est passé. Et le paramètre vbscript.regexp désigne la classe permettant de manipuler les expressions régulières. Et d'ailleurs dans la foulée, nous exploitons la propriété héritée Pattern afin de définir le motif à trouver dans la chaîne de caractères. Ce motif consiste en une forme reconnaissable, bien qu'il n'y ait pas vraiment de règle pour déceler toutes les balises Html. Dans la syntaxe des expressions régulières, nous indiquons que les occurrences à trouver commencent et se terminent nécessairement par un symbole inférieur ou supérieur (< ou >), grâce aux parenthèses. Les crochets quant à eux, permettent d'énumérer tous les caractères susceptibles de se trouver entre ces bornes remarquables. Nous énumérons donc chacun d'entre eux. Mais comme le nombre de ces caractères entre les deux symboles, peut varier d'une balise à une autre, nous indiquons que leur répétition est indéfinie, grâce au symbole de l'étoile situé juste après le crochet fermant.
Puis, nous initialisons la variable objet motif, grâce à la méthode héritée Execute. Cette dernière permet de lancer la recherche de la forme remarquable définie par le motif, sur la chaîne qui lui est passée en paramètre. Mais comme cette recherche se stoppe sur la première occurrence trouvée, nous devons initialiser une boucle de traitement, afin de réitérer la recherche jusqu'à la dernière balise Html.
- Pour ce faire, Ã la suite de la fonction, ajouter les instructions suivantes :
chaine = expReg.Replace(chaine, "")
Set motif = expReg.Execute(chaine)
Wend
purger_balises = chaine
La propriété héritée Count de l'objet motif permet de compter les occurrences trouvées, selon le motif imposé. Nous exploitons son résultat comme un critère de boucle afin de poursuivre le traitement, tant qu'elles ne sont pas toutes remplacées. En effet, à chaque passage de la boucle While, nous exploitons la méthode héritée Replace de l'objet expReg, afin de remplacer chacune de ces occurrences par une chaîne vide (""). Et avant de boucler, nous exécutons de nouveau la recherche du motif sur la chaîne. Si son résultat est toujours positif, le traitement se poursuit, sinon la boucle se termine. A l'issue, nous affectons au nom de la fonction, la variable chaine ainsi retravaillée et purgée. C'est ainsi que la variable Desc se voit affectée de son contenu nettoyé, depuis la procédure appelante nettoyer_exporter. Le code VBA complet de la fonction purger_balises est le suivant :
Function purger_balises(chaine As String) As String
Dim expReg As Object: Dim motif As Object
chaine = Replace(chaine, "<BR>", "-")
chaine = Replace(chaine, "</BR>", "-")
chaine = Replace(chaine, "</P>", "-")
chaine = Replace(chaine, " ", " ")
Set expReg = CreateObject("vbscript.regexp")
expReg.Pattern = "(<)[0-9a-zA-Z_ ?~#\/.:;'=-]*(>)"
Set motif = expReg.Execute(chaine)
While motif.Count > 0
chaine = expReg.Replace(chaine, "")
Set motif = expReg.Execute(chaine)
Wend
purger_balises = chaine
End Function
Mettre à jour des enregistrements de la base de données
Nous devons exploiter ce résultat dans la procédure nettoyer_exporter, lorsque l'image correspondant à l'enregistrement a été trouvée, afin de mettre à jour l'information de champ. C'est à nouveau une requête Sql qui doit être exécutée sur l'enregistrement en cours. Et c'est le mot clé Update qui permet de définir une requête mise à jour.
- Dans la branche Else de la procédure nettoyer_exporter, juste après l'appel de la fonction purger_balises (desc = purger_balises(desc)), ajouter les deux lignes de code VBA suivantes :
base.Execute requete
Le principe est identique au précédent, pour la suppression des données, sauf qu'il s'agit cette fois d'une actualisation de l'enregistrement en cours de lecture dans la boucle VBA. Nous désignons quelle table doit être mise à jour dans la syntaxe Sql, juste après le mot clé Update. Puis il s'agit de définir la nouvelle valeur du champ à désigner juste après le mot clé SET. Nous l'affectons au contenu de la variable desc, purgée de ses balises grâce à l'appel de la fonction. Comme il s'agit d'une variable, nous la concaténons (&) à la chaîne de la syntaxe Sql. Bien sûr cette mise à jour ne doit pas être réalisée sur tous les enregistrements de la table. C'est pourquoi nous concaténons une clause Where à la fin de la syntaxe pour spécifier l'enregistrement en cours. Et pour cela, nous imposons l'égalité avec la variable lid, concaténée elle aussi.
Cet enregistrement validé et nettoyé doit désormais être ajouté à la suite dans le fichier d'exportation. Chacune des informations de champ doit être séparée d'une autre par un caractère remarquable, comme la barre verticale (|). On appelle ce caractère un séparateur de liste pour matérialiser les colonnes.
- A la suite du code, toujours dans la branche Else, ajouter les instructions suivantes :
ligne_export = ligne_export & ligne.Fields("societes_activite").Value & "|" & ligne.Fields("societes_departement").Value & "|"
ligne_export = ligne_export & ligne.Fields("societes_ville").Value & "|" & desc & "|" & ligne.Fields("societes_photo1").Value
Nous décomposons la manoeuvre sur plusieurs lignes pour des raisons de lisibilité. Le principe consiste en effet à concaténer chaque information de champ pour l'enregistrement en cours, dans l'ordre où ils apparaissent, avec le séparateur de liste.
Pour ajouter cette ligne à la suite des autres afin d'offrir un fichier d'exportation complet et consolidé à l'issue, nous devons de nouveau accéder au fichier texte que nous avons créé en début de procédure. Il s'agit d'un accès en écriture. Mais pour pouvoir écrire à la suite sans écraser les précédentes données, le mode d'accès est Append.
- A la suite du code, toujours dans la branche Else, ajouter les instructions suivantes :
Print #1, ligne_export
Close #1
Le code est très semblable à celui que nous avons écrit pour créer le fichier exportation.txt. Seul le mode change (For Append), afin de consolider les données sans les écraser. En effet, comme ces instructions sont encapsulées dans la boucle de lecture des enregistrements, dès que l'un d'entre eux est validé, il est ajouté à la suite.
Avant de tester le bon fonctionnement du code, nous devons fermer les accès à la base de données et libérer les ressources de la mémoire.
- A la fin de la procédure nettoyer_exporter, après la boucle et avant le End Sub, ajouter les lignes de code VBA suivantes :
base.Close
Set fichier = Nothing
Set ligne = Nothing
Set base = Nothing
La méthode Close des objets Recordset et Database permet de fermer les connexions respectives. Puis, nous réaffectons tous les objets à Nothing afin de les vider de la mémoire.
- Enregistrer les modifications (CTRL + S),
- Placer le point d'insertion n'importe où dans la procédure nettoyer_exporter,
- Puis, l'exécuter en enfonçant la touche F5 du clavier,
- Basculer sur l'application Access (ALT + F11),
- Puis, double cliquer sur la table id_sorties dans le volet des objets, pour l'ouvrir,
Si vous consultez l'indication dans la petite barre de navigation, en bas de la fenêtre de la feuille de données, vous constatez que le nombre d'enregistrements a fortement chuté. Il est passé de 558 à 271. Près de 300 lignes ont donc été supprimées car leur fichier image n'existait pas. Les miniatures précisément sont au nombre de 273. Cet écart s'explique par le fait que deux d'entre elles sont en supplément et ne sont attachées à aucun enregistrement de la table Access. Enfin, vous remarquez que le champ societes_descriptif a parfaitement été purgé de toutes ses balises Html et ce, pour tous les enregistrements. Notre procédure VBA pour nettoyer la base de données a donc parfaitement fonctionné.
Il reste à vérifier que les informations ont correctement été exportées.
- Dans l'explorateur Windows, afficher le dossier de l'application Access,
- Double cliquer sur le fichier exportation.txt qui s'y trouve effectivement,
Comme vous le constatez, tous les enregistrements s'y trouvent. Pour chacun d'entre eux, vous notez que les informations de champ sont explicitement séparées par le caractère remarquable de la barre verticale, tel que nous l'avons codé.
- Fermer ce fichier texte et revenir sur l'application Access,
Sub nettoyer_exporter()
Dim ligne As Recordset: Dim base As Database
Dim nom_dossier As String: Dim nom_fichier As String
Dim lid As String: Dim desc As String
Dim ligne_export As String: Dim requete As String
Dim fichier As Object
nom_dossier = Application.CurrentProject.Path & "\images\"
Set base = Application.CurrentDb
Set ligne = base.OpenRecordset("id_sorties", dbOpenTable, dbReadOnly)
Set fichier = CreateObject("scripting.filesystemobject")
Open Application.CurrentProject.Path & "\exportation.txt" For Output As #1
Close #1
If ligne.RecordCount = 0 Then Exit Sub
ligne.MoveFirst
Do
nom_fichier = nom_dossier & ligne.Fields("societes_photo1").Value
lid = ligne.Fields("societes_id").Value
If fichier.FileExists(nom_fichier) = False Then
requete = "DELETE FROM id_sorties WHERE societes_id = " & lid
base.Execute requete
Else
desc = ligne.Fields("societes_descriptif").Value
desc = purger_balises(desc)
requete = "UPDATE id_sorties SET societes_descriptif = '" & desc & "' WHERE societes_id = " + lid
base.Execute requete
ligne_export = lid & "|" & ligne.Fields("societes_nom").Value & "|"
ligne_export = ligne_export & ligne.Fields("societes_activite").Value & "|" & ligne.Fields("societes_departement").Value & "|"
ligne_export = ligne_export & ligne.Fields("societes_ville").Value & "|" & desc & "|" & ligne.Fields("societes_photo1").Value
Open Application.CurrentProject.Path & "\exportation.txt" For Append As #1
Print #1, ligne_export
Close #1
End If
ligne.MoveNext
Loop Until ligne.EOF = True
ligne.Close
base.Close
Set fichier = Nothing
Set ligne = Nothing
Set base = Nothing
End Sub
Compacter la base de données Access par le code VBA
Le compactage consiste à réduire la taille du fichier tout en améliorant les performances, grâce à une réorganisation des données. Comme nous avons purgé les informations et que nous avons supprimé de nombreux enregistrements, il est raisonnable d'estimer que le gain ne sera pas négligeable. La taille actuelle de la base de données est de plus de 3,3Mo.
- Revenir dans l'éditeur de code VBA Access (ALT + F11),
- Après la fonction purger_balises, créer la procédure compacter, comme suit :
End Sub
Cette procédure devra être appelée à la fin du traitement, par la procédure nettoyer_exporter.
- Dans les bornes de la procédure compacter, ajouter les déclarations suivantes :
Dim fichier As Object
Une base de données peut être compactée seulement si elle n'est pas ouverte. Or notre code s'exécute précisément dans la base de données qui doit être optimisée. L'astuce consiste à créer une copie et à compacter cette dernière. C'est la raison pour laquelle nous créons plusieurs variables de type String afin de manipuler ces copies. Copier une base de données signifie être en mesure de manipuler les fichiers. C'est pourquoi nous créons une variable de type Object qui devra de nouveau instancier la classe nécessaire.
- A la suite du code, ajouter les affectations suivantes :
nom_bd = Application.CurrentDb.Name
nom_tmp = nom_bd & ".tmp"
nom_fin = Replace(nom_tmp, ".mdb.tmp", "-optimise.mdb")
Tout d'abord nous instancions la classe permettant à l'objet fichier de manipuler les fichiers du disque. Comme toujours, c'est la fonction VBA CreateObject avec le bon argument qui permet de le faire. Nous prélevons le nom de la base de données en cours avec son chemin complet, grâce à la propriété Name de la méthode CurrentDb de l'objet Application. A ce nom, nous concaténons une extension supplémentaire (".tmp"), pour le fichier de la copie. Comme la méthode de compactage demande de définir un nouveau nom de destination, nous remplaçons cette double extension par le suffixe-optimise.mdb. Le compactage génère en effet une autre copie.
Si plusieurs exécutions successives sont produites, le fichier optimisé peut déjà exister. Si c'est le cas, la méthode de compactage génèrera une erreur. Donc, nous devons d'abord le supprimer avant de compacter.
- A la suite du code, ajouter l'instruction conditionnelle suivante :
fichier.DeleteFile nom_fin
End If
Comme nous l'avons fait pour tester la présence des images, nous exploitons la propriété booléenne FileExists de l'objet fichier, sur le nom de la base de données optimisée. Si elle renvoie True, nous supprimons la copie grâce à la méthode héritée DeleteFile, en lui passant le nom du fichier.
Il ne reste plus qu'à créer la copie, compacter cette dernière et supprimer la copie qui aura servi d'intermédiaire pour le compactage.
- Pour ce faire, Ã la suite du code, ajouter les instructions VBA suivantes :
DBEngine.CompactDatabase nom_tmp, nom_fin
fichier.DeleteFile nom_tmp
La méthode héritée copyfile de l'objet fichier permet de copier un fichier source défini en premier paramètre, vers un fichier de destination passé en deuxième paramètre. C'est ensuite la méthode CompactDatabase de l'objet VBA Access DBEngine qui permet de réaliser le compactage de la base passée en premier paramètre, dans une base de données optimisée dont le nom lui est passé en second paramètre. Enfin, la méthode héritée DeleteFile de l'objet fichier permet de supprimer la copie temporaire qui a servi de lien pour compacter la base de données.
Pour tester le résultat, cette procédure doit être appelée.
- A la fin de la procédure nettoyer_exporter, avant le End Sub, réaliser l'appel de la procédure par son nom, soit : compacter ,
- Enregistrer les modifications puis exécuter le code,
Encore une fois, le traitement est transparent pour l'utilisateur. Néanmoins, si vous ouvrez le dossier de l'application Access dans l'explorateur Windows, vous notez la présence de la copie optimisée. Et comme vous le remarquez, la différence de taille est importante. Nous avons divisé le poids du fichier par trois. Voilà donc un traitement automatisé et optimisé par le code Visual Basic fort intéressant pour les bases de données.
Le code complet de la procédure compacter, est le suivant :
Sub compacter()
Dim nom_bd As String: Dim nom_tmp As String: Dim nom_fin As String
Dim fichier As Object
Set fichier = CreateObject("scripting.FilesystemObject")
nom_bd = Application.CurrentDb.Name
nom_tmp = nom_bd & ".tmp"
nom_fin = Replace(nom_tmp, ".mdb.tmp", "-optimise.mdb")
If fichier.FileExists(nom_fin) = True Then
fichier.DeleteFile nom_fin
End If
fichier.copyfile nom_bd, nom_tmp, True
DBEngine.CompactDatabase nom_tmp, nom_fin
fichier.DeleteFile nom_tmp
End Sub
Sur Facebook
Sur Youtube
Les livres
Contact
Mentions légales
