par , 27/12/2021 à 08h48 (4237 Affichages)
C'est bien connu, une base de données Access a tendance à gonfler assez rapidement si on ajoute directement les pièces jointes (documents, images, etc..) dans les enregistrements à l'aide du champ de type Pièce jointe.
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 réaliser l'interface et le code VBA permettant de gérer depuis Access les pièces jointes placées à l'extérieur de la base.
Cependant donc, après la mise en production de l'application, les utilisateurs se rendent compte au bout d'un certain temps que la base de données a beaucoup grossi, ce qui entraîne notamment un ralentissement important de certaines opérations, comme la recherche, ou la mise à jour de données.
Pour résoudre ce genre de problème, j'ai pensé qu'il serait utile de montrer comment implémenter une fonction permettant d'extraire les pièces jointes d'une table pour les enregistrer dans le dossier de son choix, et de copier dans une autre table les chemins de ces différents fichiers sur le disque, et ainsi créer les liens entre la base de données et les documents, images, etc..
On supprimera également à la fin le champ pièce jointe de la table, permettant ainsi de compacter la base pour qu'elle retrouve une taille acceptable.
Le développeur sera alors dans les meilleures conditions pour réaliser une gestion de pièces jointes plus adaptée.
I. Exemple de table
On part d'une table toute simple permettant d'enregistrer des informations sur des articles, et d'ajouter des documents à l'aide d'un champ de type Pièce jointe :
Nom de la colonne |
Type de données |
Description |
IdArticle |
NuméroAuto |
Identifiant de l'article |
RefArticle |
Texte |
Référence de l'article |
LibelleArticle |
Texte |
Libellé de l'article |
CategorieArticle |
Entier long |
Identifiant de la catégorie de l'article |
PrixUnitaire |
Monétaire |
Prix unitaire de l'article |
FicheArticle |
Pièce jointe |
Document(s) constituant la fiche de l'article |
... |
... |
... |
On ne présente ici que les colonnes importantes de la table.
Aperçu de la table en mode création :
Aperçu de quelques données avant l'extraction :
On constate que le dernier champ de la table permet d'enregistrer les pièces jointes dans la base de données.
II. Fonction d'extraction des pièces jointes
Elle permet d'extraire les pièces jointes pour les copier à l'emplacement de son choix, et d'enregistrer les chemins d'accès aux différents fichiers dans une table secondaire.
Arguments de la fonction
- nomTable : nom de la table contenant les pièces jointes ;
- nomChampID : nom du champ identifiant de la table ;
- nomChampPJ : nom du champ de type Pièce jointe ;
- nomTablePJ : nom de la table contenant les adresses des fichiers sur le disque ;
- nomChampChemin : nom du champ contenant les adresses des fichiers sur le disque ;
- cheminDossier : chemin du dossier de destination pour les fichiers.
La fonction ExtrairePiecesJointes 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 les pièces jointes ;
- 2. création de la table permettant d'enregistrer les chemins des fichiers sur le disque ;
- 3. parcours des enregistrements de la table contenant les pièces jointes ;
- ----- 3.1. pour chaque enregistrement : parcours des pièces jointes contenues dans le champ P.J. ;
- --------- 3.1.1 pour chaque pièce jointe : enregistrement sur le disque du fichier correspondant ;
- ----------3.1.2 pour chaque pièce jointe : copie dans la table secondaire (table P.J.) de son chemin sur le disque et de l'identifiant de la table principale ;
- ----------3.1.3 pour chaque pièce jointe : suppression de la pièce jointe de la table ;
- 4. suppression du champ Pièce jointe 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 les pièces jointes, puis on crée la table destinée à enregistrer les adresses des fichiers sur le disque :
1 2 3 4 5 6 7 8 9
| 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(nomTablePJ) Then ' si la table destinée à enregistrer les emplacements des fichiers n'existe pas
dbs.Execute "create table " & nomTablePJ & "(" & nomchampChemin & " CHAR, " & nomChampID & " INTEGER);", dbFailOnError ' création de la table permettant d'enregistrer les adresses des fichiers extraits sur le disque
Else ' sinon
dbs.Execute "delete * from " & nomTablePJ & ";", 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 chemins des fichiers sur le disque, et on crée le dossier de destination :
1 2 3
| Set rstPJ2 = dbs.OpenRecordset(nomTablePJ) ' ouverture du recordset basé sur la nouvelle table destinée à enregistrer les chemins des pièces jointes
If Dir(cheminDossier, vbDirectory) = "" Then MkDir cheminDossier ' si le dossier n'existe pas on le crée |
Pour chaque enregistrement de la table principale, on récupère le recordset du champ pièce jointe, et on sauvegarde chaque fichier à l'emplacement souhaité, avec copie de son chemin d'accès dans la table secondaire, et suppression de la pièce jointe de la table principale :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
| Do Until rst.EOF ' on parcourt les enregistrements de la table
' on récupère le recordset lié au champ pièce jointe de l'enregistrement courant
Set rstPJ1 = rst(nomChampPJ).Value
' on parcourt les pièces jointes du champ pièce jointe de l'enregistrement
Do Until rstPJ1.EOF
' on compose le chemin complet du fichier sur le disque
cheminFichier = cheminDossier & rstPJ1("FileName")
' on s'assure que le fichier n'existe pas déjà avant de le sauvegarder
If Dir(cheminFichier) <> "" Then Kill (cheminFichier) ' s'il existe déjà, on le supprime
rstPJ1("FileData").SaveToFile cheminFichier ' on enregistre le fichier à l'emplacement spécifié
rstPJ2.AddNew ' ajout du chemin complet du fichier à la nouvelle table avec l'identifiant de la table principale
rstPJ2.Fields(nomchampChemin) = cheminFichier
rstPJ2.Fields(nomChampID) = rst.Fields(nomChampID)
rstPJ2.Update
rstPJ1.Delete ' suppression de la pièce jointe de la table
rstPJ1.MoveNext ' prochaine pièce jointe
Loop
' prochain enregistrement de la table principale
rst.MoveNext
Loop |
On suppose pour simplifier que les noms des fichiers sont tous différents.
Code complet
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89
| Option Compare Database
Option Explicit
Public Function ExtrairePiecesJointes(nomTable As String, nomChampID As String, nomChampPJ As String, nomTablePJ As String, nomchampChemin As String, cheminDossier As String) As Boolean
On Error GoTo err_ExtrairePiecesJointes
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 de type pièce jointe
Dim rst As DAO.Recordset ' variable recordset pour faire référence à la table
Dim rstPJ1 As DAO.Recordset ' variable recordset pour faire référence au jeu d'enregistrements du champ de type pièce jointe
Dim rstPJ2 As DAO.Recordset ' variable pour faire référence à la nouvelle table destinée à enregistrer les emplacements des fichiers
Dim cheminFichier As String ' chemin complet du fichier sur le disque
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(nomTablePJ) Then ' si la table destinée à enregistrer les emplacements des fichiers n'existe pas
dbs.Execute "create table " & nomTablePJ & "(" & nomchampChemin & " CHAR, " & nomChampID & " INTEGER);", dbFailOnError ' création de la table permettant d'enregistrer les adresses des fichiers extraits sur le disque
Else ' sinon
dbs.Execute "delete * from " & nomTablePJ & ";", dbFailOnError ' on vide la table si elle existe déjà
End If
Set rstPJ2 = dbs.OpenRecordset(nomTablePJ) ' ouverture du recordset basé sur la nouvelle table destinée à enregistrer les chemins des pièces jointes
If Dir(cheminDossier, vbDirectory) = "" Then MkDir cheminDossier ' si le dossier n'existe pas on le crée
Do Until rst.EOF ' on parcourt les enregistrements de la table
' on récupère le recordset lié au champ pièce jointe de l'enregistrement courant
Set rstPJ1 = rst(nomChampPJ).Value
' on parcourt les pièces jointes du champ pièce jointe de l'enregistrement
Do Until rstPJ1.EOF
' on compose le chemin complet du fichier sur le disque
cheminFichier = cheminDossier & rstPJ1("FileName")
' on s'assure que le fichier n'existe pas déjà avant de le sauvegarder
If Dir(cheminFichier) <> "" Then Kill (cheminFichier) ' s'il existe déjà, on le supprime
rstPJ1("FileData").SaveToFile cheminFichier ' on enregistre le fichier à l'emplacement spécifié
rstPJ2.AddNew ' ajout du chemin complet du fichier à la nouvelle table avec l'identifiant de la table principale
rstPJ2.Fields(nomchampChemin) = cheminFichier
rstPJ2.Fields(nomChampID) = rst.Fields(nomChampID)
rstPJ2.Update
rstPJ1.Delete ' suppression de la pièce jointe de la table
rstPJ1.MoveNext ' prochaine pièce jointe
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 pièce jointe
tdf.Fields.Delete (nomChampPJ) ' suppression du champ de type pièce jointe de la table principale
ExtrairePiecesJointes = True ' on renvoie Vrai indiquant que l'extraction s'est bien passée
exit_ExtrairePiecesJointes: ' gestion de la sortie de la fonction
On Error Resume Next
' fermeture et libération des variables objet
rstPJ2.Close
Set rstPJ1 = Nothing
Set rstPJ2 = Nothing
Set rst = Nothing
Set tdf = Nothing
Set dbs = Nothing
Exit Function
err_ExtrairePiecesJointes: ' si une erreur s'est produite
MsgBox "Erreur d'exécution " & Err.Number & vbNewLine & vbNewLine & Err.Description, vbExclamation ' on affiche le numéro et le message de l'erreur
Resume exit_ExtrairePiecesJointes ' on va à l'étiquette exit_ExtrairePiecesJointes 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 |
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.
III. 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 fichiers, on doit lui transmettre certains arguments :
Exemples d'arguments passés à la fonction
- nomTable : "T_Article" ;
- nomChampID : "IdArticle" ;
- nomChampPJ : "FicheArticle" ;
- nomTablePJ : "T_PieceJointe" ;
- nomChampChemin : "CheminFichier" ;
- cheminDossier : dossier "Fiches Articles" du répertoire de l'application.
1 2 3 4 5 6 7 8 9 10 11
| Dim cheminDossier As String
cheminDossier = CurrentProject.Path & "\Fiches Articles\" ' emplacement du dossier de destination
' appel de la fonction
If ExtrairePiecesJointes("T_Article", "IdArticle", "FicheArticle", "T_PieceJointe", "CheminFichier", cheminDossier) Then ' si l'extraction s'est bien passée
MsgBox "Extraction réussie !", vbExclamation ' on affiche un message pour indiquer que l'exportation s'est bien passée
Else ' sinon
MsgBox "Problème lors de l'extraction !", vbCritical ' on signale un problème
End If
Application.RefreshDatabaseWindow ' rafraîchit le panneau de navigation pour afficher la nouvelle table |
On peut bien sûr ajouter dans le code d'autres appels à la fonction pour extraire les pièces jointes de plusieurs tables à la fois.
Contenu de la table principale, après l'extraction des fichiers et la suppression du champ de type Pièce jointe :
Création de la table T_PieceJointe et ajout pour chaque article du chemin des pièces jointes :
Dossier contenant les fichiers exportés :
IV. Conclusion
Après avoir implémenté dans son application la fonction d'extraction des fichiers, le développeur pourra ensuite facilement exporter ses pièces jointes à l'emplacement de son choix, et créer les liens entre la base de données et les différents documents. Il ne lui restera alors plus qu'à réaliser l'interface et le code VBA pour gérer ses pièces jointes depuis Access.