Générer des tâches planifiées depuis Access

Jean-Philippe ANDRÉ

Dans le cadre de ce tutoriel, on se limitera à générer des tâches planifiées à usage unique, et qui "s'autodétruisent" à la fin de leur réalisation. L'idée générale est de montrer qu'en partant d'une seule tâche planifiée créée manuellement, Access est en mesure de générer "seul" un ensemble de tâches planifiées, et devenir ainsi quasiment autonome sur un poste de travail dédié.
Commentez cet article : Commentez

- Titre : Générer des tâches planifiées depuis Access

- Auteur : Jean-Philippe ANDRÉ

- Parution : 10 novembre 2009

- Licence : Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright ® 2009 Jean-Philippe ANDRÉ. 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. Description d'une tâche planifiée

- II. Gestion des tâches dans Access

- II-A. Structure de la table T_BATCH

- II-B. Structure de la table T_DAILY_BATCH

- III. Descente des informations depuis la table de toutes les tâches planifiées vers la table du jour

- IV. Génération d'un fichier batch

- V. Génération d'une tâche planifiée sous DOS

- VI. Rattachement du fichier batch à la tâche planifiée

- VII. Historisation des tâches planifiées en fin de journée

- VIII. Code VBA complet

- VIII-A. Fonction de récupération des données de la table de paramètres _ADMIN_

- VIII-B. Fonction d'écriture dans un fichier

- VIII-C. Descendre les batch du jour depuis T_BATCH dans la table T_DAILY_BATCH

- VIII-D. Générer les fichiers batch et les rattacher aux tâches planifiées

- VIII-E. Historisation des tâches planifiées du jour

- IX. Base à télécharger

- X. Conclusion

- XI. Remerciements

I - Description d'une tâche planifiée

Les exemples de créations de tâches planifiées sont déjà expliqués dans cette partie d'article

II - Gestion des tâches dans Access

Pour définir l'ensemble des tâches planifiées du jour dans Access, je vous propose ici de stocker les différents batch dans deux tables.
Dans l'une l'ensemble des tâches planifiées qui peuvent être lancée par la base (nommée T_BATCH).
Dans l'autre, on stockera les tâches planifiées du jour (respectivement T_DAILY_BATCH).
Les éléments de base qui peuvent définir les tâches planifiées sont du type :

- Identifiant de la tâche

- Heure de lancement de la tâche

- Fichier à ouvrir/exécuter

- Nom de la tâche planifiée

Pour l'identifiant de la tâche, on peut se contenter du Numéro Auto de la table T_BATCH. Dans le cadre d'une remise à zéro, voir la méthode proposée ici. Sinon, une numérotation spécifique peut aussi être mise en place. L'important étant d'avoir une clé primaire (donc pas de doublon) sur ce champ.

L'heure de lancement est donnée sous la forme "hh:mm:ss".

Le chemin du fichier à générer est disponible dans la table de paramètres _ADMIN_ de la base exemple.

Le nom de la tâche sera une combinaison des éléments précédents.

II-A - Structure de la table T_BATCH

Pour un suivi des tâches planifiées un peu plus précis, on ajoutera les éléments suivants :

- Base qui doit être lancée par la tâche planifiée

- Jour de lancement

- Fréquence de lancement (période entre deux lancements)

- Date de dernier lancement réalisé

- Date du prochain lancement prévu

Le chemin du fichier qui doit être exécuté se trouve dans la table _ADMIN_.
Les jours de lancement de la tâche se présentent sous la forme 1|2|3. Chaque jour de lancement est séparé par le délimiteur " | ", déjà utilisé dans les tutoriels déjà évoqués. Exemple

- 1|3|5 indique un lancement les lundi, mercredi et vendredi de chaque semaine

- 2 indique un lancement uniquement le mardi

La fréquence de lancement correspond à une information de type lancement hebdomadaire, mensuel, trimestriel, etc. Exemple

- S_FREQU "m" et I_JUMP à 1 pour un lancement mensuel

- S_FREQU "ww" et I_JUMP à 2 pour un lancement bimensuel

- S_FREQU "m" et I_JUMP à 2 pour un lancement bimestriel

NB : la fréquence correspond aux valeurs utilisables dans la fonction DateAdd( )

La date du dernier lancement correspond à la date de dernière exécution effective de la tâche
La date du prochain lancement correspond à la date calculée sur la base de la fréquence et la date du dernier lancement.

Vue de la table T_BATCH

II-B - Structure de la table T_DAILY_BATCH

On se limitera aux éléments basiques pour créer une tâche planifiée :

- Identifiant de la tâche

- Heure de lancement de la tâche

- Fichier à ouvrir/exécuter

- Paramètre à passer au fichier par batch

- Information sur le statut de la tâche planifiée (New, Proc, Done ou Err)

Vue de la table T_DAILY_BATCH

III - Descente des informations depuis la table de toutes les tâches planifiées vers la table du jour

La règle de gestion de génération des tâches planifiées répond à deux critères :
- La date de lancement de la tâche planifiée est la date du jour
Ou
- La date du prochain lancement est antérieure à la date du jour (cas des dates tombant les jours fériés par exemple)
La syntaxe de la requête sera ici :

INSERT INTO T_DAILY_BATCH (ID_BATCH, ID_MPROCESS, NM_SOURCE, DT_TREATMENT, NB_STEP, STATUS)
SELECT T_BATCH.ID_BATCH, T_BATCH.ID_MPROCESS, T_BATCH.NM_SOURCE, T_BATCH.DT_TREATMENT, T_BATCH.NB_STEP, '_' AS STATUS
FROM T_BATCH
WHERE (((InStr([s_Days],Weekday(Now(),2)))>0)) OR (((T_BATCH.D_NEXT_LAUNCH)<=Now()))

NB : le paramètre 2 dans la fonction Weekday permet de spécifier qu'on considère lundi comme le premier jour de la semaine (1 signifiant dimanche en tant que premier jour de la semaine)

IV - Génération d'un fichier batch

Générer un fichier batch n'est ni plus ni moins que de générer un fichier texte, dont l'extension finale sera .bat. Le contenu de ce fichier batch sera la ligne de commande qu'on souhaite exécuter (pour lancer une macro d'une base par exemple).
Voir le tutoriel à ce sujet pour Excel et Access

Dans le cadre de notre étude, où on veut simplement exécuter une macro nommée MaMacro070000, voila le contenu du fichier que l'on souhaitera générer

Dim strBatch As String
strBatch ="start /WAIT msaccess.exe """ &  "C:\temp\Mabase.mdb" & """ /xMaMacro070000"

V - Génération d'une tâche planifiée sous DOS

Le lancement de ligne de commande DOS se fait par la fonction Shell().
L'ensemble des fonctions liées aux tâches planifiées se fait en commençant par SCHTASKS. Lien vers la documentation MSDN à ce sujet.

Ici on cherche à créer une tâche planifiée, à lancement unique, dont on spécifiera les éléments.

Pour qu'une tâche planifiée soit bien ajoutée par Windows, les paramètres à renseigner sont dans notre cas :
- Login de l'utilisateur qui souhaite lancer la tâche
- Mot de passe de l'utilisateur
- L'heure de lancement de la tâche
- Le chemin du fichier batch qu'on va exécuter
- Le nom que l'on souhaite donner à la tâche planifiée

Exemple de ligne de commande :

Fichier 1.bat lancé à 07h00.

Dim strCreateScheduleTask As String
strCreateScheduleTask = "SCHTASKS /Create /RU LoginPiou "
strCreateScheduleTask = strCreateScheduleTask & "/RP PWDPiou "
strCreateScheduleTask = strCreateScheduleTask & "/SC once "
strCreateScheduleTask = strCreateScheduleTask & "/ST 07:00:00 "
strCreateScheduleTask = strCreateScheduleTask & "/TR " & """\""" & "C:\temp\1.bat"" /TN R" & "_" & "1" & "_" & "070000"
Shell strinput

VI - Rattachement du fichier batch à la tâche planifiée

Comme vu dans le paragraphe précédent, il suffit de passer dans la partie /TR de la ligne de commande SCHTASKS le chemin du fichier à exécuter.

VII - Historisation des tâches planifiées en fin de journée

Pour ne pas avoir des doublons d'un jour sur l'autre, il est nécessaire de vider la table T_DAILY_BATCH. Au lieu de supprimer purement et simplement les enregistrements, l'auteur propose ici de les déverser dans une troisième table T_HISTO_BATCH, pour en garder une trace et faire ultérieurement des statistiques dessus par exemple.
De plus, pour ne pas multiplier des fichiers à profusion, on ajoutera au module d'historisation une suppression des fichiers .bat en fin de journée.

Les 4 étapes de ce module seront donc :
- déversement des tâches planifiées du jour dans l'historique des tâches planifiées
- mise à jour des dates de dernier lancement
- suppression des fichiers batch
- suppression des enregistrements dans la table des tâches du jour

VIII - Code VBA complet

Pour éviter de stocker les valeurs dans des constantes, on privilégie ici une gestion par table de paramètres. Cette méthode est détaillée dans le tutoriel suivant.

VIII-A - Fonction de récupération des données de la table de paramètres _ADMIN_

La fonction utilisée est la suivante

Public Function RecupererAdmin(strIntitule As String) As String
RecupererAdmin = Nz(DLookup("Valeur", "_ADMIN_", "Intitule='" & strIntitule & "'"), "")
End Function

VIII-B - Fonction d'écriture dans un fichier

De la même façon, on utilise des fonctions d'écriture dans les fichiers.

Public Function EcrireFichierTexte(strText As String, strpathfile As String) As Boolean
Dim F As Integer
On Error GoTo fin
F = FreeFile
Open strpathfile For Append As #F
Print #F, strText
Close #F
EcrireFichierTexte = True
Exit Function
fin:
If err.Number = 61 Then ' accès en écriture du fichier non accepté
EcrireFichierTexte = False
End If
End Function

VIII-C - Descendre les batch du jour depuis T_BATCH dans la table T_DAILY_BATCH

Sub GenererBatchduJour()
Dim strSQL As String

strSQL = "INSERT INTO T_DAILY_BATCH (ID_BATCH, ID_MPROCESS, NM_SOURCE, DT_TREATMENT, NB_STEP, STATUS) " & _
"SELECT T_BATCH.ID_BATCH, T_BATCH.ID_MPROCESS, T_BATCH.NM_SOURCE, T_BATCH.DT_TREATMENT, T_BATCH.NB_STEP, '_' AS STATUS" & _
" FROM T_BATCH" & _
" WHERE (((InStr([s_Days],Weekday(Now(),2)))>0)) OR (((T_BATCH.D_NEXT_LAUNCH)<=Now()));"
CurrentDb.Execute strSQL

End Sub

VIII-D - Générer les fichiers batch et les rattacher aux tâches planifiées

Sub GenerationBatchAll(ID_BATCH As String)
Dim RS As DAO.Recordset
Dim strSQL As String
Dim strBatch As String
Dim PathFile As String
Dim strFileContent As String
'requete tous ou seulement ceux passés en paramètre
strSQL = "SELECT * FROM T_DAILY_BATCH WHERE "
If ID_BATCH = "0" Then
strSQL = strSQL & "LEN(STATUS)=1;"
Else
strSQL = strSQL & "ID_BATCH=" & ID_BATCH & ";"
End If

Set RS = CurrentDb.OpenRecordset(strSQL)
Do Until RS.EOF
'on peut distinguer les différents types de fichiers à gérer
Select Case RS.Fields("NM_SOURCE").Value
'gestion possible au cas par cas de la base ou du fichier à générer (Excel par exemple)
Case Else:
strFileContent = "start /WAIT msaccess.exe """ & _
RecupererAdmin("Emplacement" & RS.Fields("NM_SOURCE").Value) & """ ; """ & _
"M" & "|" & RS.Fields("ID_BATCH").Value & ";" & _
RS.Fields("ID_MPROCESS").Value & ";" & _
Replace(RS.Fields("DT_TREATMENT").Value, ":", "") & ";" & _
RS.Fields("NB_STEP").Value & ";" & Format(Now, "yyyyMMdd") & """"

'pour ne pas avoir à écrire deux fois la même ligne si le fichier est déjà existant, on le détruit.
PathFile = RecupererAdmin("CommonDirectoryBatch") & "R" & "_" & _
RS.Fields("NM_SOURCE").Value & "_" & _
RS.Fields("ID_MPROCESS").Value & "_" & _
Replace(RS.Fields("DT_LAUNCH").Value, ":", "") & ".bat"
If Dir(PathFile) <> "" Then
Kill PathFile
End If
'génération du fichier batch
EcrireFichierTexte strBatch, PathFile
'rattachement du fichier batch dans les taches planifiées
strbatch = "SCHTASKS /Create "
strBatch = strBatch & "/RU " & RecupererAdmin("LoginScheduler")
strBatch = strBatch & " /RP " & RecupererAdmin("PwdScheduler")
strBatch = strBatch & " /SC once /ST "
strBatch = strBatch & RS.Fields("DT_LAUNCH").Value
strBatch = strBatch & " /TR "
strBatch = strBatch & """\""" & PathFile & """ /TN R"
strBatch = strBatch & "_" & RS.Fields("NM_SOURCE").Value
strBatch = strBatch & "_" & RS.Fields("ID_MPROCESS").Value
strBatch = strBatch & "_" & Replace(RS.Fields("DT_LAUNCH").Value, ":", "")
Shell (strBatch)
End Select
RS.Edit
RS.Fields("STATUS").Value = "New"
RS.Update
RS.MoveNext
Loop
RS.Close

End Sub

VIII-E - Historisation des tâches planifiées du jour

Sub HistorisationDailyBatch()
Dim strSQL As String
Dim strdate As String
Dim RS As New ADODB.Recordset

strdate = CStr(Format(Now(), "mm/dd/yyyy"))

strSQL = "INSERT INTO T_HISTO_BATCH SELECT * FROM T_DAILY_BATCH;"
CurrentDb.Execute strSQL

strSQL = "UPDATE T_BATCH SET D_LAST_LAUNCH = #" & strdate & "# WHERE ID_BATCH IN (" & _
" SELECT ID_BATCH FROM T_DAILY_BATCH WHERE STATUS='Done');"
CurrentDb.Execute strSQL

strSQL = "UPDATE T_BATCH SET D_NEXT_LAUNCH = DATEADD(S_FREQ,I_JUMP,D_LAST_LAUNCH) WHERE D_LAST_LAUNCH = #" & strdate & "#"
CurrentDb.Execute strSQL

strSQL = "SELECT * FROM T_DAILY_BATCH WHERE STATUS='Done';"
RS.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Do Until RS.EOF
PathFile = RecupererAdmin("CommonDirectoryBatch") & "R" & "_" & _
RS.Fields("NM_SOURCE").Value & "_" & _
RS.Fields("ID_MPROCESS").Value & "_" & _
Replace(RS.Fields("DT_LAUNCH").Value, ":", "") & ".bat"
If Dir(PathFile) <> "" Then
Kill PathFile
End If
RS.MoveNext
Loop

strSQL = "DELETE * FROM T_DAILY_BATCH WHERE STATUS='Done';"
CurrentDb.Execute strSQL

End Sub

IX - Base à télécharger

Vous trouverez une base exemple liée au présent tutoriel ici.

X - Conclusion

On peut aller plus loin dans la gestion des différents types de traitements souhaités. Cette approche assez générale permet déjà de se rendre compte des opportunités qu'offre Access en utilisant la fonction Shell().

XI - Remerciements

Je tiens à remercier l'équipe de Developpez.com pour la qualité du site, jacques_jean, Arkham46 et Tofalu pour la relecture de cet article, et tous ceux qui contribuent à l'entraide autour du développement dans le cadre personnel et professionnel.