Créer un planificateur de tâches avec Access

Denis Hulo

Objectif : apprendre à créer un planificateur de tâches périodiques avec Access.

Niveau requis : confirmé.

Commentez cet article : Commentez

- Titre : Créer un planificateur de tâches avec Access

- Auteur : Denis Hulo

- Parution : 19 septembre 2019

- Mise à jour : 3 octobre 2019

- Public visé :

- Licence : Copyright ® 2019 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. Principe de fonctionnement du planificateur

- III. Tables pour enregistrer les données

- III-A. Table T_Tache

- III-B. Table T_ParametresProg

- III-C. Table T_JournalTache

- IV. Formulaire F_ProgrammateurTaches

- IV-A. Planification des tâches

- IV-B. Procédure d'exécution des tâches

- IV-C. Exécution des tâches sur Timer

- V. Procédures associées aux tâches

- V-A. SaveDB

- V-B. TransferSpreadSheet

- V-C. DisplayAlert

- VI. Outil à télécharger

- VII. Remerciements

I - Introduction

Un premier tutoriel expliquant comment générer des tâches planifiées a déjà été publié par Jean-Philippe André.

L'objectif de cet article est d'expliquer comment réaliser un outil Access permettant d'enregistrer dans une table des tâches périodiques (sauvegardes de la base, affichages d'alertes, transferts de données…) en les associant à des procédures et à une périodicité, pour ensuite les exécuter au bon moment en utilisant la méthode Run d'Access.

Pour cela, nous commencerons par décrire le principe de fonctionnement de cet utilitaire, puis la structure des objets et le code VBA nécessaires pour réaliser votre propre planificateur de tâches.

L'article comportera donc quatre parties principales :

- principe de fonctionnement du planificateur ;

- tables pour enregistrer les données ;

- formulaire F_ProgrammateurTaches pour planifier et exécuter les tâches ;

- procédures associées aux tâches.

Tâches

II - Principe de fonctionnement du planificateur

L'utilisateur définit au début, sur le formulaire de démarrage, la période x de contrôle des tâches en millisecondes, période appelée aussi intervalle de minuterie.

Ensuite, le programmateur parcourt toutes les x ms la liste des tâches à exécuter.

Il vérifie pour chacune des tâches si elle est active et si sa date de prochaine exécution mémorisée est déjà passée ou égale à la date d'aujourd'hui. Si c'est le cas, il exécute la tâche puis passe à la suivante.

Pour interrompre le processus, il suffit de mettre l'intervalle de minuterie à 0 ou de fermer le formulaire.

Le processus de contrôle et d'exécution des différentes tâches se poursuit tant que le formulaire et donc la base restent ouverts.

Formulaire F_ProgrammateurTaches

Access n'étant pas vraiment multitâches, lancer des processus un peu gourmands en ressources système, pourrait empêcher l'utilisateur d'effectuer d'autres opérations dans le même temps.

III - Tables pour enregistrer les données

III-A - Table T_Tache

Elle contient les informations sur les tâches à exécuter :

Champ

Type de données

Description

IdTache

Numéro-auto

Numéro d'enregistrement

IntituleTache

Texte

Intitulé de la tâche

Periodicite

entier long

périodicité en nombre de secondes, minutes, heures, jours, semaines, mois, ou années

UniteTemps

Texte

Unité de temps (seconde, minute, heure, jour, mois, semaine, année)

DateDebutPeriodicite

Date/heure

Date et heure de début de la périodicité ou de première exécution de la tâche

DateProchaineExecution

Date/heure

Date et heure de prochaine exécution de la tâche

DateFinPeriodicite

Date/heure

Date et heure de fin de la périodicité

NomProcedure

Texte

Nom de la procédure à exécuter

ArgumentProcedure

Texte

Argument de la procédure à exécuter

Active

Booléen

Indique si la tâche est active ou non

Exemples de tâches à exécuter :

Sauvegarde de la base Access toutes les 30 minutes.

Transfert tous les mois des données sur les factures du mois précédent.

Alerte pour relancer un client toutes les semaines pour payer sa facture.

Bien entendu, la périodicité doit toujours être supérieure à la durée maximale de la tâche.

Table T_Tache

Pour exécuter la procédure, on utilise la méthode Run avec le nom de la procédure en argument.

III-B - Table T_ParametresProg

Elle permet d'enregistrer les paramètres du programmateur :

Champ

Type de données

Description

IntervalleMinuterie

Entier long

Intervalle de minuterie exprimé en millisecondes

EtatProg

Texte

État du programmateur

Exemples d'états du programmateur :

Programmateur actif le 02/09/2019 à 15:00:00

Lancement de la procédure « SaveDB » le 02/09/2019 à 15:30:00

Fin de la procédure « SaveDB » le 02/09/2019 à 15:31:00

III-C - Table T_JournalTache

Elle permet d'enregistrer l'historique de toutes les tâches exécutées :

Champ

Type de données

Description

IdJournalTache

NuméroAuto

Identifiant de la tâche

DateExecutionTache

Date/heure

Date et heure d'exécution de la tâche

IdTache

Entier long

Identifiant de la tâche

On peut ainsi vérifier si une tâche programmée dans le passé a bien été exécutée.

Exemples d'enregistrement :

Tâche n°1 (Sauvegarde de la base) le 02/09/2019 à 15:00:00

Tâche n°2 (Transfert dans Excel des factures du mois précédent) le 02/09/2019 à 15:30:00

IV - Formulaire F_ProgrammateurTaches

Le formulaire lié à la table T_ParametresProg, comporte une zone de texte txtIntervalleMinuterie, pour définir l'intervalle de temps entre deux lancements de la procédure RunTasks et une autre txtEtatProg, en bas du formulaire, pour indiquer l'état du programmateur et aussi les procédures en cours d'exécution. Ces paramètres sont enregistrés dans la table.

Il contient également un sous-formulaire SF_Taches pour afficher la liste des tâches enregistrées dans la base.

Formulaire F_ProgrammateurTaches

IV-A - Planification des tâches

Pour mieux comprendre la programmation périodique des taches, illustrons-la en représentant les lancements sur un planning mensuel.

En reprenant l'exemple des alertes toutes les semaines pour relancer le client avant échéance, on obtient pour le mois de septembre 2019 :

Planning des alertes

On remarque que la procédure d'alerte DisplayAlert est exécutée toutes les semaines durant les périodes d'activité du programmateur, hors week-end.

Si maintenant on place 2 jours supplémentaires d'inactivité (affichés en rouge) en dehors des week-ends durant ce mois de septembre :

Planning des alertes avec période d'inactivité

On constate que le programmateur lance la procédure d'alerte le jour suivant immédiatement cette période d'inactivité, et la date de prochaine exécution sera calculée comme s'il n'y avait pas eu d'interruption, pour éviter des décalages.

La date de prochaine exécution de la tâche étant égale à la date de la 1re exécution à laquelle on ajoute le nombre de récurrences jusqu'à aujourd'hui plus 1.

Planning complet des tâches pour le mois de septembre 2019 :

Planning des tâches

La 1re tâche « Sauvegarde de la base » a une périodicité de 30 minutes et s'exécute donc plusieurs fois par jour, mais l'échelle de temps étant le jour, elle ne permet pas de représenter tous les lancements de cette tâche.

Planning complet des tâches avec période d'inactivité :

Planning des tâches avec période d'inactivité

IV-B - Procédure d'exécution des tâches

Extrait de l'aide :

Vous pouvez utiliser la méthode Run pour exécuter une procédure ou une fonction spécifiée par l’utilisateur ou Microsoft Access.

Syntaxe
expression.Run (Procédure, Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10, Arg11, Arg12, Arg13, _ Arg14_, Arg15, Arg16, Arg17, Arg18, Arg19, Arg20, Arg21, Arg22, Arg23, Arg24, Arg25, Arg26, _ Arg27_, Arg28, Arg29, Arg30)

expression est une variable qui représente un objet Application.
…

Dans notre cas, on utilisera au plus qu'un argument, et la ligne de code ressemblera donc à :

application.Run NomProcedure, ArgumentProcedure

La procédure d'exécution des tâches les parcourt une à une :

Déroulé condensé de la procédure RunTasks

- on ouvre le Recordset contenant les informations sur les tâches actives ;

- pour chaque enregistrement, on teste si la date de prochaine exécution de la tâche est inférieure ou égale à maintenant ;

- si c'est le cas, on exécute la procédure associée à la tâche avec la méthode Run ;

- puis on met à jour la date de sa prochaine exécution.

Procédure RunTasks

Public Sub RunTasks()
' Procédure d'exécutions des tâches actives enregistrées
Dim db As DAO.Database ' référence à la base courante
Dim rs As DAO.Recordset ' référence au recordset lié à la table T_Tache
Dim dateProchaineExecution As Date ' date de prochaine exécution de la tâche
Dim DateExecution As Date ' date actuelle d'exécution

Set db = CurrentDb ' référence à la base active

' ouverture du recordset lié aux tâches actives à planifier
Set rs = db.OpenRecordset("select * from T_Tache where Active;", dbOpenDynaset)

Do Until rs.EOF ' on parcourt les différentes tâches

If IsNull(rs!dateProchaineExecution) Then ' si pas de date de prochaine exécution
rs.Edit
rs!dateProchaineExecution = rs!DateDebutPeriodicite ' on met à jour le champ avec la date de la 1re exécution
rs.Update
End If

DateExecution = Now() ' on copie la date et l'heure actuelles dans la variable

' si la date et l'heure de prochaine exécution sont arrivées ou passées
If (rs!dateProchaineExecution <= DateExecution) Then
' si la date prévue pour la dernière exécution n'est pas dépassée
If (rs!DateFinPeriodicite >= Now) Or IsNull(rs!DateFinPeriodicite) Then

' On indique dans la barre d'état du programmateur le démarrage de la procédure à la date et à l'heure actuelles
Me!txtEtatProg.Value = "Lancement de la tâche """ & rs!IntituleTache & """ |  " & Now()

' il faut lancer la tâche et recalculer la date de prochaîne exécution de la tâche
If Nz(rs!ArgumentProcedure) = "" Then ' si pas d'argument à la procédure
Run (rs!NomProcedure) ' on exécute la procédure à l'aide de la méthode run
Else ' sinon
' on exécute la procédure à l'aide de la méthode run avec un argument supplémentaire
Run rs!NomProcedure, rs!ArgumentProcedure
End If

' On indique dans la barre d'état du programmateur la fin de la procédure à la date et à l'heure actuelles
Me!txtEtatProg.Value = "Fin de la tâche """ & rs!IntituleTache & """ |  " & Now()

End If

' puis on recalcule la date de prochaîne exécution
dateProchaineExecution = EvalDateProchaineExecution(rs!DateDebutPeriodicite, DateExecution, rs!Periodicite, rs!UniteTemps)

' si la date de prochaîne exécution de la tâche dépasse la date prévue pour sa dernière exécution
If dateProchaineExecution > Nz(rs!DateFinPeriodicite, #1/1/3000#) Then
rs.Edit
rs!DateFinPeriodicite = rs!dateProchaineExecution ' on enregistre la date de la dernière exécution
rs!Active = False ' on déactive la tâche
rs.Update
Else
rs.Edit
rs!dateProchaineExecution = dateProchaineExecution ' on enregistre la date de prochaine exécution
rs.Update
End If

' on enregistre dans le journal l'identifiant de la tâche et sa date d'exécution
HistoriserTache rs!IdTache, DateExecution

End If

rs.MoveNext ' enregistrement suivant

Loop

' libération des variables
rs.Close
Set rs = Nothing

Set db = Nothing

End Sub

La fonction de calcul de la date de prochaine exécution donne un résultat qui ne prend pas en compte les éventuels jours d'inactivité de l'entreprise.

Gros plan sur le code employé pour lancer une procédure :

' il faut lancer la tâche et recalculer la date de prochaîne exécution de la tâche
If Nz(rs!ArgumentProcedure) = "" Then ' si pas d'argument à la procédure
Application.Run (rs!NomProcedure) ' on exécute la procédure à l'aide de la méthode run
Else ' sinon
' on exécute la procédure à l'aide de la méthode run avec un argument supplémentaire
Application.Run rs!NomProcedure, rs!ArgumentProcedure
End IF

L'exécution des tâches s'effectue sur l'événement Timer du formulaire F_ProgrammateurTaches .

IV-C - Exécution des tâches sur Timer

Pour paramétrer le Timer du formulaire, on a besoin de définir l'intervalle de minuterie en millisecondes. Par exemple, pour exécuter la procédure RunTasks toutes les 60 secondes, on saisit sur cette propriété 60 000 (ms).

Propriétés Timer

Il faut que le Timer ait une périodicité significativement inférieure à celle des tâches, pour nos exemples 1 minute devrait convenir.

Ensuite, on doit placer la procédure d'exécution des tâches sur l'événement Timer :

Private Sub Form_Timer()
Me.txtEtatProg.Value = "Programmateur actif le  " & Date & " à " & Time() ' On affiche l'état actuel du programmateur
RunTasks ' exécution des tâches sur timer
Me.SF_Taches.Requery ' on rafraîchit le sous-formulaire des tâches
End Sub

Pour définir manuellement l'intervalle d'exécution de la procédure sur Timer, on peut aussi saisir une valeur dans la zone de texte IntervalleMinuterie, représentant l'intervalle de temps entre 2 exécutions exprimé en millisecondes :

Procédure sur l'événement après mise à jour de la zone de texte

Private Sub txtIntervalleMinuterie_AfterUpdate()
' définit l'intervalle de minuterie après mise à jour de la zone de texte
' si 0 arrêt du timer
' copie de la valeur de la zone de texte dans la propriété TimerInterval du formulaire
Me.TimerInterval = Me.txtIntervalleMinuterie
If Me.txtIntervalleMinuterie > 0 Then
' On affiche l'état actuel du programmateur : actif
Me.txtEtatProg.Value = "Programmateur actif |  " & Now()
Me.SF_Taches.Locked = True ' on verrouille les données durant l'exécution
Else
' On affiche l'état actuel du programmateur : à l'arrêt
Me.txtEtatProg.Value = "Programmateur arrêté |  " & Now()
Me.SF_Taches.Locked = False ' on déverrouille les données
End If
End Sub

Comme la routine RunTasks met à jour la date de prochaine exécution de la tâche, on verrouille les données lors de l'exécution de la procédure sur minuterie pour éviter les conflits d'écriture.

V - Procédures associées aux tâches

On décrit les procédures contenues dans le module M_Taches et permettant d'exécuter les différentes tâches.

V-A - SaveDB

La procédure sauvegarde la base locale ou la dorsale si elle possède des tables liées :

Procédure SaveDB

Public Sub SaveDB()
' Procédure de sauvegarde des données Access
Dim fso As Object ' référence à fso
Dim cheminFichier As String ' variable pour le chemin complet du fichier sur le disque
Dim nomFichier As String ' variable pour le nom du fichier
Dim nomDossier As String ' variable pour le nom du dossier de sauvegarde
Dim db As DAO.Database ' variable liée à la base de donnée
Dim tb As TableDef ' variable pour faire référence à une table

Set db = CurrentDb ' référence à la base active
Set tb = db.TableDefs("T_Facture") ' table locale ou liée

Set fso = CreateObject("Scripting.FileSystemObject") ' Création de l'objet fso

nomDossier = CurrentProject.Path & "\Sauvegardes" ' dossier de sauvegarde

If tb.Connect = "" Then ' pas de table liée
cheminFichier = CurrentProject.FullName ' chemin complet du fichier Access
nomFichier = Left(CurrentProject.Name, InStr(CurrentProject.Name, ".") - 1) ' nom du fichier Access sans l'extension
Else ' une table liée
' la chaîne de connection est de la forme ;DATABASE=C:\...\NomBase.accdb"
cheminFichier = Mid(tb.Connect, 11) ' on extrait le chemin complet du fichier de la base liée
nomFichier = Mid(tb.Connect, InStrRev(tb.Connect, "\") + 1, InStr(tb.Connect, ".") - InStrRev(tb.Connect, "\") - 1) ' on extrait le nom de la base liée
End If

If Dir(nomDossier, vbDirectory) = "" Then ' si le dossier n'existe pas
fso.CreateFolder nomDossier ' création du dossier de sauvegarde
End If

' copie du fichier Access dans le dossier de sauvegarde
fso.CopyFile cheminFichier, nomDossier & "\" & nomFichier & " - " & Format(Date, "yyyy-mm-dd") & ".accdb"

' Affichage du message de succès de la sauvegarde
MsgBox "Sauvegarde du fichier réalisée avec succès !", vbExclamation

' On libère les variables objet
Set fso = Nothing
Set tb = Nothing
Set db = Nothing

End Sub

Exemple d'appel de la procédure :

Application.Run "SaveDB"

On utilise la méthode CopyFile de l'objet FileSystemObject (FSO), car elle permet de copier non seulement la base courante, mais aussi la dorsale dans le cas de tables liées.

V-B - TransferSpreadSheet

La procédure transfère dans un fichier Excel les données des factures du mois précédent :

Procédure TransferSpreadSheet

Public Sub TransferSpreadSheet()
' Procédure de transfert des données de la requête R_FactureMois dans un fichier Excel
Dim fso As Object ' référence à fso
Dim cheminFichier As String ' variable pour le chemin complet du fichier sur le disque
Dim nomDossier As String ' variable pour le nom du dossier de sauvegarde

Set fso = CreateObject("Scripting.FileSystemObject")

nomDossier = CurrentProject.Path & "\Factures mensuelles" ' dossier de sauvegarde
cheminFichier = nomDossier & "\Factures " & Format(DateAdd("m", -1, Date), "yyyy-mm") & ".xlsx" ' chemin complet du fichier Access
If Dir(nomDossier, vbDirectory) = "" Then ' si le dossier n'existe pas
fso.CreateFolder nomDossier ' création du dossier de sauvegarde
End If
' transfert de la table Access vers le fichier Excel
DoCmd.TransferSpreadSheet acExport, acSpreadsheetTypeExcel12Xml, "R_FactureMoisPrecedent", cheminFichier, True
' Affichage du message de succès du transfert
MsgBox "Transfert de la table réalisé avec succès !", vbExclamation
' On libère la variable fso
Set fso = Nothing
End Sub

Exemple d'appel de la procédure :

Application.Run "TransferSpreadSheet"

On utilise la méthode TransferSpreadSheet de l'objet DoCmd pour transférer les données dans le fichier Excel.

V-C - DisplayAlert

La procédure affiche le message d'alerte passé en argument :

Procédure Alert

Public Sub DisplayAlert(msg As String)
' on affiche le message d'alerte passé en argument
MsgBox msg, vbExclamation, "Alerte !"
End Sub

Exemple d'appel de la procédure :

Application.Run "DisplayAlert", "Relance facture référence ""F-00003"" !"

Dans ce cas, la procédure possède un argument msg, il faut donc l'ajouter lors de l'appel de la routine avec la méthode Run.

VI - Outil à télécharger

Le formulaire de démonstration F_ProgrammateurTaches s'exécute au démarrage de la base. Si vous souhaitez intégrer ce type de formulaire dans votre base, il faudra qu'il reste ouvert en permanence pour ne pas interrompre le timer et la procédure d'exécution des tâches. Pour cela, vous pouvez soit empêcher sa fermeture, soit le masquer en mettant sa propriété visible à false.

Pour démarrer le programmateur, saisir un entier supérieur à 0 dans la zone de texte Intervalle minuterie, puis cliquer sur une autre zone de texte dans la liste des tâches.

La base jointe décrite dans le tutoriel est au format accdb.

VII - Remerciements

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