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()))

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

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

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

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

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

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

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