Extraire les pièces jointes d'une base de données Access

Denis Hulo

Objectif : extraire les pièces jointes enregistrées dans une base de données Access pour les copier dans le dossier de son choix.

Niveau requis : avancé.

Commentez cet article : Commentez

- Titre : Extraire les pièces jointes d'une base de données Access

- Auteur : Denis Hulo

- Parution : 2 février 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. Exemple de table

- III. Fonction d'extraction des pièces jointes

- IV. Implémentation de la fonction

- V. Conclusion

- VI. Fichier à télécharger

- VII. Remerciements

I - Introduction

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èmes, 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 la colonne pièce jointe de la table, permettant ainsi de compacter la base pour qu'elle retrouve une taille acceptable.

On sera alors dans les meilleures conditions pour réaliser une gestion de pièces jointes plus adaptée.

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

Description de l'article

CategorieArticle

Entier long

Identifiant de la catégorie d'article

PrixUnitaire

Monétaire

Prix unitaire

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 :

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 de la table permet d'enregistrer les pièces jointes dans la base de données.

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

1. nomTable : nom de la table contenant les pièces jointes ;

2. nomChampID : nom du champ identifiant de la table ;

3. nomChampPJ : nom du champ de type Pièce jointe ;

4. nomTablePJ : nom de la table contenant les adresses des fichiers sur le disque ;

5. nomChampChemin : nom du champ contenant les adresses des fichiers sur le disque ;

6. 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 :

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
' création de la table permettant d'enregistrer les adresses des fichiers extraits sur le disque
dbs.Execute "create table [" & nomTablePJ & "]([IdPieceJointe] COUNTER, [" & nomchampChemin & "] TEXT, [" & nomChampID & "] INTEGER);", dbFailOnError
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 :

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 :

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

Module M_ExtrairePiecesJointes

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

' 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(nomTablePJ) Then  ' si la table destinée à enregistrer les emplacements des fichiers n'existe pas
' création de la table permettant d'enregistrer les adresses des fichiers extraits sur le disque
dbs.Execute "create table [" & nomTablePJ & "]([IdPieceJointe] COUNTER, [" & nomchampChemin & "] TEXT, [" & nomChampID & "] INTEGER);", dbFailOnError
Else ' sinon
dbs.Execute "delete * from [" & nomTablePJ & "];", dbFailOnError ' on vide la table si elle existe déjà
End If

' ouverture du recordset basé sur la nouvelle table destinée à enregistrer les chemins des pièces jointes
Set rstPJ2 = dbs.OpenRecordset(nomTablePJ)

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é
' ajout du chemin complet du fichier à la nouvelle table avec l'identifiant de la table principale
rstPJ2.AddNew
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

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

Public Function testExtraction()
Dim cheminDossier As String

cheminDossier = CurrentProject.Path & "\Fiches Articles\"  ' emplacement du dossier de destination
' appel de la fonction : ' si l'extraction s'est bien passée
If ExtrairePiecesJointes("T_Article", "IdArticle", "FicheArticle", "T_DocumentArticle", "CheminFichier", cheminDossier) Then
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

' 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 fichiers, on doit lui transmettre certains arguments :

Exemples d'arguments passés à la fonction :

1. nomTable : "T_Article" ;

2. nomChampID : "IdArticle" ;

3. nomChampPJ : "FicheArticle" ;

4. nomTablePJ : "T_DocumentArticle" ;

5. nomChampChemin : "CheminFichier" ;

6. cheminDossier : dossier "Fiches Articles" du répertoire de l'application.

Dim cheminDossier As String

cheminDossier = CurrentProject.Path & "\Fiches Articles\"  ' emplacement du dossier de destination
' appel de la fonction : ' si l'extraction s'est bien passée
If ExtrairePiecesJointes("T_Article", "IdArticle", "FicheArticle", "T_DocumentArticle", "CheminFichier", cheminDossier) Then
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

' rafraîchit le panneau de navigation pour afficher la nouvelle table
Application.RefreshDatabaseWindow

Contenu de la table principale, après l'extraction des fichiers et la suppression du champ de type Pièce jointe :

Aperçu de la table après extraction

Création de la table T_DocumentArticle et ajout pour chaque article du chemin des pièces jointes :

Aperçu de la table T_DocumentArticle

Dossier contenant les fichiers exportés :

Dossier de destination

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.

Dans ce cas, le code va créer une table destinée à enregistrer les liens pour chaque table contenant les pièces jointes.

Si on prend par exemple les tables de départ :

- T_Article(IdArticle, RefArticle, .., FicheArticle)

- T_Intervention(IdIntervention, RefIntervention, .., FicheIntervention)

On peut extraire les pièces jointes contenues dans ces deux tables en effectuant deux appels successifs à la fonction :

ExtrairePiecesJointes "T_Article", "IdArticle", "FicheArticle", "T_DocumentArticle", "CheminFichier", "c:\Fiches Articles\"
ExtrairePiecesJointes "T_Intervention", "IdIntervention", "FicheIntervention", "T_DocumentIntervention", "CheminFichier", "c:\Fiches Interventions\"

Pour obtenir après extraction les tables suivantes :

- T_Article(IdArticle, RefArticle, ..) ;

- T_DocumentArticle(IdPieceJointe, CheminFichier, IdArticle) ;

- T_Intervention(IdIntervention, RefIntervention, ..) ;

- T_DocumentIntervention(IdPieceJointe, CheminFichier, IdIntervention).

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 implémenté dans son application la fonction d'extraction des fichiers, on 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 restera alors plus qu'à réaliser l'interface et le code VBA pour gérer ses pièces jointes depuis Access.

VI - Fichier à télécharger

La base de données exemple contient la table avec les pièces jointes et la fonction permettant de les extraire.

Pour tester l'extraction, exécuter la fonction testExtraction contenue dans le module de l'application.

VII - Remerciements

Je tiens à remercier Jean-Philippe André et Pierre Fauconnier pour m'avoir conseillé pour la réalisation de cet article, ainsi que escartefigue pour sa relecture.