Copie de sauvegarde automatisée 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 :


Copie de sauvegarde automatisée en VBA

Cette petite formation VBA Access est l'occasion de mettre en pratique des notions apprises au travers de la formation sur l'accès aux fichiers, afin de réaliser des copies de sauvegarde des bases, avant de manipuler les données. Cette copie de sauvegarde, pour sécuriser les données, doit permettre de les archiver automatiquement à l'ouverture de la base. C'est une macro Access qui permettra de désigner le code à déclencher.

La base de données Access
Pour les besoins de l'application, nous partons d'une base de données existante.

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

Cette base de données est constituée de trois tables. La plus importante, la table societes, recense des activités de sorties organisées par départements et types de loisirs. Nous l'avions exploitée pour les besoins de la formation VBA Access permettant de filtrer et d'extraire les données. Etant donné que nous considérons ces données comme sensibles, un code VBA Access doit se déclencher automatiquement à l'ouverture de la base de données. L'exécution de la copie de sauvegarde doit se réaliser de façon transparente pour l'utilisateur.

Module de code VBA Access
Jusqu'alors nous avions développé nos programmes VBA dans des procédures événementielles rattachées à des contrôles de formulaire. Nous accédions à l'éditeur de code du formulaire grâce à la feuille de propriétés des contrôles. Mais le code VBA que nous souhaitons développer ici est indépendant. Nous devons donc l'écrire dans un module. Et nous devons créer ce module.

• Cliquer sur l'onglet Créer en haut de la fenêtre Access pour activer son ruban,
• Dans la section Macros et code, cliquer sur le bouton Module,

Nous basculons ainsi dans l'éditeur de code Visual Basic Access. Au centre, figure une page de code vierge. C'est à cet endroit que nous créerons notre procédure. Notre module (Module 1) apparaît dans l'arborescence de l'explorateur de projet, sur la gauche de la fenêtre.

• Cliquer sur le menu Affichage en haut de l'éditeur de code,
• Dans la liste, choisir Fenêtre propriétés si elle n'est pas visible dans l'éditeur,
• Dans la propriété (Name) de cette fenêtre, saisir code à la place de Module 1 et valider,

Nous renommons ainsi explicitement le module qui accueillera notre procédure de code VBA.

• Dans la feuille de code, sous la ligne Option Compare Database, ajouter l'instruction suivante :

Option Explicit

Visual Basic est un langage de programmation orienté objets très permissif. Si des variables ne sont pas déclarées, VBA s'en accommode et les déclare à la volée. Mais leur typage, variant, laisse à désirer. Pour réaliser un programme propre, nous demandons à VBA de ne pas accepter les variables utilisées lorsqu'elles sont mal déclarées. C'est l'instruction Option Explicit qui déclenche ce contrôle. Le débogueur VBA alertera alors le programmeur sur les erreurs induites.


Code VBA Access pour la sauvegarde des données
La procédure de code que nous devons créer est en fait une fonction. Ainsi elle pourra être appelée de l'extérieur, par une macro notamment. Le mot clé utilisé pour déclarer une fonction est Function.

• Dans la feuille de code, sous les instructions précédentes, créer la fonction comme suit :

Function sauvegarde()

End Function

Le mot-clé Function est suivi du nom que nous attribuons à cette dernière, soit sauvegarde ici. Toute fonction doit être bornée par l'instruction End Function. Lorsqu'elle sera appelée à s'exécuter par une macro externe, c'est le code écrit entre ses bornes qui se déclenchera.

Le code précisément doit se charger de créer une copie de sauvegarde de la base de données en cours. Pour cela, nous devons commencer par déclarer la variable objet, qui une fois qu'elle sera affectée, permettra de manipuler les fichiers et plus précisément, celui de notre base de données pour la dupliquer.

• Entre les bornes de la fonction, ajouter la déclaration de variable suivante :

Dim fichier As Object

Toute variable déclarée doit ensuite être affectée. Dans le cas d'une variable objet, il s'agit d'instancier une classe. Ainsi la variable héritera des propriétés et méthodes, pour manipuler les fichiers du système, ici en l'occurrence. C'est le mot clé Set qui est utilisé en VBA pour initialiser et affecter une variable objet. Et comme nous l'avions vu dans la formation VBA Access pour accéder aux fichiers et aux dossiers, c'est la fonction VBA CreateObject qui permet de définir le type d'objet, selon l'instruction passée en argument.

• Après la déclaration de la variable, ajouter la ligne de code suivante :

Set fichier = CreateObject("scripting.FilesystemObject")

Comme l'enseigne l'aide en ligne, l'instruction scripting.FilesystemObject, permet d'instancier un objet permettant de manipuler les fichiers et dossiers. Désormais notre objet fichier propose la méthode copyfile qui permet de copier un fichier sous un autre nom, dans un emplacement à définir.

• A la suite du code, ajouter l'instruction suivante :

fichier.copyfile Application.CurrentDb.Name, Application.CurrentDb.Name & ".SRo", True

La méthode CurrentDb de l'objet VBA Application permet de pointer sur la base de données en cours. Sa propriété Name renvoie alors le chemin d'accès complet à la base de données, avec le nom du fichier. Donc dans le premier argument de la méthode copyfile, nous désignons la base de données en cours, comme le fichier source à copier par Application.CurrentDb.Name. Le deuxième argument de la méthode consiste à désigner l'emplacement cible. Comme nous souhaitons réaliser une copie locale, nous récupérons le chemin complet auquel nous ajoutons, par concaténation (&), l'extension .SRo. Cette extension n'étant pas connue par le système d'exploitation, elle ne pourra pas s'exécuter au double clic et les données seront préservées. Le dernier paramètre de la méthode copyfile est un booléen. Défini à True, il permet de réaliser une copie en écrasant le fichier de destination s'il existe déjà. Ainsi, nos copies de sauvegarde se mettront à jour toutes seules.

Tout objet de programmation déclaré et instancié doit ensuite être déchargé, afin de libérer proprement les ressources et éviter de les engorger en mémoire. C'est le mot clé Nothing par affectation qui permet de décharger un objet.

• Ajouter l'instruction suivante à la suite du code :

Set fichier = Nothing



Tout le code VBA permettant de sauvegarder les données tient en quatre lignes, déclaration de variable comprise. Néanmoins, le travail n'est pas terminé puisqu'il s'agit de le déclencher à l'ouverture de la base de données.

Déclencher un code à l'ouverture d'une base de données Access
Contrairement à VBA Excel, il n'existe pas de procédure événementielle par défaut dans VBA Access, pour déclencher un code à l'ouverture du fichier. Par contre, une macro si elle porte le nom Autoexec se déclenchera à l'ouverture de la base de données. En conséquence nous devons créer une macro nommée Autoexec, qui ordonne l'exécution du code de la fonction que nous avons créée.

• Réaliser le raccourci clavier ALT + F11 pour basculer sur la fenêtre de l'application Access,
• Dans le ruban Créer, cliquer sur le bouton Macro, pour afficher l'éditeur de macros,
• Dans l'éditeur, choisir l'action ExécuterCode à l'aide de la liste déroulante,
• Dans la zone Nom de la fonction, saisir sauvegarde(),

Il s'agit en effet du nom que nous avons donné à la fonction lors de sa création dans l'éditeur de code Visual Basic.

• Réaliser le raccourci clavier CTRL + S pour sauvegarder,
• Dans la boîte de dialogue, taper précisément le nom Autoexec et valider,

Comme notre macro se nomme Autoexec, elle doit s'activer au démarrage de la base de données. Comme son action est de déclencher le code de la fonction Sauvegarde, la copie de sauvegarde doit se produire au démarrage d'Access. C'est ce que nous allons vérifier.

• Fermer la base de données Access,
• Puis, l'ouvrir de nouveau,
• Ensuite, accéder au contenu du dossier de la base Access à l'aide de l'explorateur Windows,

Comme vous le constatez, une copie de sauvegarde avec l'extension définie, a bien été créée à la minute précise, correspondant à la dernière ouverture de la base de données.

Copie de sauvegarde en VBA Excel
Bien que l'application change, le code VBA reste quasiment identique, à l'exception de certains objets propres. Donc nous allons répliquer le code VBA Access dans Excel afin là aussi, de réaliser une copie de sauvegarde, à l'ouverture du classeur.

• Télécharger le classeur tableau-sorties.xlsx en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer le contenu si nécessaire,

Comme l'illustre la capture ci-dessus, il s'agit des données de la base Access précédente. Elles ont été importées dans la feuille Sorties de ce classeur. Un classeur Excel par défaut ne gère pas les macros et le code VBA par extension, pour des raisons de sécurité. Nous devons donc l'enregistrer sous un format spécifique.

• Cliquer sur l'onglet Fichier en haut de la fenêtre Excel puis choisir Enregistrer sous,
• Cliquer ensuite sur Parcourir,
• Dans la boîte de dialogue qui suit, choisir Classeur Excel (prenant en charge les macros)(*.xlsm), dans la zone Type,
• Puis, cliquer sur Enregistrer,

Désormais, nous pouvons développer un code VBA qui ne sera pas neutralisé par l'application. De même, nous allons modifier les paramètres de sécurité pour ne pas nuire au déroulement du programme. Pour ce faire, nous avons besoin du ruban développeur. La formation pour débuter la programmation en VBA Excel rappelle notamment, comment afficher ce ruban.

• Cliquer sur l'onglet Développeur pour activer son ruban,
• Dans la section Code, cliquer sur le bouton Sécurité des macros,
• Cocher la case Activer toutes les macros et valider,
• Réaliser ensuite le raccourci ALT + F11 pour basculer dans l'éditeur de code VBA Excel,

Comme nous l'avions appris dans la formation pour créer une facturation clients automatisée en VBA Excel, il existe un gestionnaire d'événements associé au classeur Excel. Plus précisément, des événements sont associés à l'objet ThisWorkbook qui désigne le classeur en cours. Et parmi ces événements, Open est l'événement qui intercepte l'ouverture d'un classeur pour déclencher un code VBA Excel. Nous allons donc créer cette procédure événementielle. Il n'est pas nécessaire ici, d'utiliser une macro comme dans Access.

• Double cliquer sur l'élément ThisWorkbook dans l'explorateur de projet sur la gauche de l'éditeur de code,

Nous affichons ainsi la page de code VBA attachée à ce classeur, au centre de l'écran.

• En haut de la feuille de code, dérouler la première liste déroulante,
• Puis, choisir l'objet Workbook,

L'événement associé par défaut est l'évènement Open. C'est la raison pour laquelle se crée automatiquement la procédure : Private Sub Workbook_Open(). Tout code VBA saisi entre les bornes de cette procédure se déclenchera à l'ouverture du classeur.

• Coller le code VBA Access précédemment écrit entre les bornes de cette procédure,

Quelques modifications sont à apporter dans la mesure où la méthode CurrentDb de l'objet Application, est fort logiquement propre à VBA Access. Le chemin d'accès à un classeur Excel peut être reconstruit à l'aide des propriétés Path et Name de l'objet ThisWorkbook. Nous avons besoin de reconstruire ce chemin pour la copie de sauvegarde.

• Sous la déclaration de la variable fichier, ajouter la déclaration suivante :

Dim chemin As String

Nous nommons la variable chemin et la déclarons comme un String pour pouvoir mémoriser une chaîne de caractères, celui du chemin d'accès au classeur en l'occurrence. C'est justement ce que nous allons coder maintenant, en affectant cette variable.

• Sous l'affectation de la variable fichier, ajouter le code de l'affectation de la variable chemin :

chemin = ThisWorkbook.Path & "\" & ThisWorkbook.Name

ThisWorkbook.Path renvoie le chemin d'accès jusqu'au dossier contenant le classeur. Il manque le nom du fichier. Nous le concaténons à ce chemin (&), après avoir ajouté un antislash (& "\") grâce à la propriété Name de l'objet ThisWorkbook.

La fonction CreateObject pour créer l'objet permettant de manipuler les fichiers, reste la même. Donc sa méthode copyfile est identique. Mais il faut remplacer les arguments des chemins source et destination, par celui du chemin d'accès mémorisé dans la variable.

• Modifier le code de la méthode copyfile comme suit :

fichier.copyfile chemin, chemin & ".SRo", True

Nous conservons l'instruction de fin de code pour décharger la variable objet. Le code est déjà terminé. Ecrit dans la bonne procédure événementielle, il est donc censé réaliser la copie de sauvegarde du classeur à l'ouverture et ce, de façon transparente pour l'utilisateur.

PrivateSub Workbook_Open()
Dim fichier As Object
Dim chemin As String

Set fichier = CreateObject("scripting.FilesystemObject")
chemin = ThisWorkbook.Path & "\" & ThisWorkbook.Name

fichier.copyfile chemin, chemin & ".SRo", True
Set fichier = Nothing
End Sub

• Enregistrer les modifications (CTRL + S),
• Basculer sur la feuille du classeur (ALT + F11),
• Fermer le classeur (CTRL + W),
• Puis, le rouvrir,

En apparence rien ne se produit, puisque nous n'avons programmé aucune alerte pour l'utilisateur. Mais si vous affichez le contenu du dossier à l'aide de l'explorateur Windows, vous constatez que la copie a bien été réalisée par le code VBA Excel, que nous venons de concevoir. Pour finir, nous avons préconisé de nombreux raccourcis claviers dans ce support. Sachez que vous pouvez les retrouver dans le support de formation Excel sur les raccourcis claviers.