Ok, "one" approach is to do the following.
Create a database that contains an AutoExec Macro. This Macro will call a Procedure that executes your queries, generates reports, emails queries/excel files, etc.
Next, have a PC that will be dedicated to run this Access database at predefined times during the day. I would have it run each day, m,t,w,th,f. Then within the database determine if it is EOM.
Once the database is openned it will automaticall launch the Procedure referenced under the AutoExec Macro.
The Procedure, can have some of the follwoing contents.
-At the begininng of the procedure, have a check to see if it is EOM. There are various ways to perform this task using a FOR Loop and incrementing DateAdd() is one approach. Or, a more simple mechanism is the build a database table with the EOM Date Values. In some cases, this may be simplier. So, check if it is EOM, if it is, DoCmd.Quit, Else then do your query checks, reports, emails,etc.
- I Highly recommend having a logging procedure so you can monitor the activity of the automated program. My approach is to have a table that records all actions of the program, then write them to a tblLog table. Good for reference and see some statistics about queries and what was printed or emailed.
- At the end of the procedure be sure to have a docmd.Quit to exit MS Access.
htwh,
Below is a code snippet...
Public Function Check_Data()
Dim ThisDB As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim rsReports As DAO.Recordset
Dim lc_Query As DAO.QueryDef
Dim lcTempStr As String
Dim lcEMailAddress As String
Dim lcSubject As String
Dim lcSubject2 As String
Dim lcMessage As String
Dim lcMessageText2 As String
Dim lcObjectType As String
Dim lcObjectName As String
Dim lcFormatType As String
Dim lcSQL As String
Dim lcSQLLog As String
Dim lcComment As String
Dim ldPriorDay As Date
Dim llCheck As Boolean
Dim lcTimeHour As String
Dim Icnt As Integer
Dim lcQryStr As String
Dim ldTemp As Date
Dim lcQueryName As String
Dim lcTimeMin As String
Dim lcTime As String
Dim lcReportID As String
Dim llTestCall As Boolean
Dim lcBeneAccount As String
Dim lnAmount As Double
Dim lcAccountNumber As String
Dim lcUserGroup As String
Dim lcTempString As String
Set ThisDB = CurrentDb
'Get Current Time - Used to Determine Which Reports per User
lcTimeHour = Hour(Time())
If Len(Trim(lcTimeHour)) = 1 Then
lcTimeHour = "0" & lcTimeHour
End If
lcTimeMin = Minute(Time())
If Len(Trim(lcTimeMin)) = 1 Then
lcTimeMin = "0" & lcTimeMin
End If
lcTime = lcTimeHour & lcTimeMin
'Clear Log of Any Old Data (less than 30 days) - Each Thursday - Day 5.
If WeekDay(Date) = 5 Then
lcObjectName = "N/A"
lcTempStr = DateAdd("d", -30, Date)
lcSQL = "DELETE FROM tbl_LOG WHERE LOG_DATE < #" & lcTempStr & "#"
ThisDB.Execute lcSQL, dbFailOnError
If ThisDB.RecordsAffected <> 0 Then
Icnt = ThisDB.RecordsAffected
lcComment = "Log Table Cleared of Old Data Prior to " & lcTempStr
lcSQL = "INSERT INTO tbl_Log (REPORT_TIME,QUERY_NAME,RECORD_COUNT,COMMENTS) "
lcSQL = lcSQL & "VALUES ('" & lcTime & "','" & lcObjectName & "'," & Icnt & ",'" & lcComment & "')"
ThisDB.Execute lcSQL, dbFailOnError
lcTempStr = ""
lcComment = ""
End If
End If
'Do Not Run Reports on Saturday or Sunday.
If WeekDay(Date) = 7 Or WeekDay(Date) = 1 Then 'Saturday or Sunday
lcObjectName = "N/A"
Icnt = 0
lcComment = "Saturday or Sunday - No Reports Generated. " & lcTempStr
lcSQL = "INSERT INTO tbl_Log (REPORT_TIME,QUERY_NAME,RECORD_COUNT,COMMENTS) "
lcSQL = lcSQL & "VALUES ('" & lcTime & "','" & lcObjectName & "'," & Icnt & ",'" & lcComment & "')"
ThisDB.Execute lcSQL, dbFailOnError
DoCmd.Quit
End If
'Get List of Reports to Run for Specified Time.
lcSQL = "SELECT tbl_Report_Notifier.REPORT_ID, tbl_Reports.REPORT_Name, tbl_Reports.REPORT_Message, "
lcSQL = lcSQL & "tbl_Report_Notifier.REPORT_TIME "
lcSQL = lcSQL & "FROM tbl_Reports INNER JOIN tbl_Report_Notifier "
lcSQL = lcSQL & "ON tbl_Reports.REPORT_ID = tbl_Report_Notifier.REPORT_ID "
lcSQL = lcSQL & "GROUP BY tbl_Report_Notifier.REPORT_ID, tbl_Reports.REPORT_Name, "
lcSQL = lcSQL & "tbl_Reports.REPORT_Message, tbl_Report_Notifier.REPORT_TIME "
lcSQL = lcSQL & "HAVING tbl_Report_Notifier.REPORT_TIME='" & lcTime & "';"
Set rsReports = ThisDB.OpenRecordset(lcSQL, dbOpenSnapshot)
If rsReports.RecordCount <> 0 Then
'Loop Through List of Reports for Designated Time (0800, 1600 or 1630)
lcObjectType = "acSendQuery"
lcFormatType = "acFormatXLS"
rsReports.MoveFirst
Do While Not rsReports.EOF
lcReportID = rsReports("REPORT_ID"
lcMessage = rsReports("REPORT_Message"
lcSubject = "Daily EMail Alert: " & rsReports("REPORT_Name"
'Get List of All EMails for That Report at That Time.
lcEMailAddress = GetEMail(lcReportID, lcTime)
....
....
'Within this section you can simply Print a Report.
Steve Medvid
"IT Consultant & Web Master"
Chester County, PA Residents
Please Show Your Support...
