monthly reports

[SpandexBobcat]

Hi gang,

The problem(s) are such:

1. I have a number of reports that I want to be automatically generated on a monthly or fortnightly basis, with no intervention. These reports will be based on present, simple queries. I know that access knows the present date but how do I get this (seemingly simple) operation to work?

2. One of these reports must print out only entries that are over month oold from the 'date' date.... can you please tell me how to also do this?

I am novice when it comes to this so the easiest explanation would be the best...

Regards,

SB

 

[smedvid]

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 = &quot;DELETE FROM tbl_LOG WHERE LOG_DATE < #&quot; & lcTempStr & &quot;#&quot;
ThisDB.Execute lcSQL, dbFailOnError
If ThisDB.RecordsAffected <> 0 Then
Icnt = ThisDB.RecordsAffected
lcComment = &quot;Log Table Cleared of Old Data Prior to &quot; & lcTempStr
lcSQL = &quot;INSERT INTO tbl_Log (REPORT_TIME,QUERY_NAME,RECORD_COUNT,COMMENTS) &quot;
lcSQL = lcSQL & &quot;VALUES ('&quot; & lcTime & &quot;','&quot; & lcObjectName & &quot;',&quot; & Icnt & &quot;,'&quot; & lcComment & &quot;')&quot;
ThisDB.Execute lcSQL, dbFailOnError
lcTempStr = &quot;&quot;
lcComment = &quot;&quot;
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 = &quot;N/A&quot;
Icnt = 0
lcComment = &quot;Saturday or Sunday - No Reports Generated. &quot; & lcTempStr
lcSQL = &quot;INSERT INTO tbl_Log (REPORT_TIME,QUERY_NAME,RECORD_COUNT,COMMENTS) &quot;
lcSQL = lcSQL & &quot;VALUES ('&quot; & lcTime & &quot;','&quot; & lcObjectName & &quot;',&quot; & Icnt & &quot;,'&quot; & lcComment & &quot;')&quot;
ThisDB.Execute lcSQL, dbFailOnError
DoCmd.Quit
End If

'Get List of Reports to Run for Specified Time.
lcSQL = &quot;SELECT tbl_Report_Notifier.REPORT_ID, tbl_Reports.REPORT_Name, tbl_Reports.REPORT_Message, &quot;
lcSQL = lcSQL & &quot;tbl_Report_Notifier.REPORT_TIME &quot;
lcSQL = lcSQL & &quot;FROM tbl_Reports INNER JOIN tbl_Report_Notifier &quot;
lcSQL = lcSQL & &quot;ON tbl_Reports.REPORT_ID = tbl_Report_Notifier.REPORT_ID &quot;
lcSQL = lcSQL & &quot;GROUP BY tbl_Report_Notifier.REPORT_ID, tbl_Reports.REPORT_Name, &quot;
lcSQL = lcSQL & &quot;tbl_Reports.REPORT_Message, tbl_Report_Notifier.REPORT_TIME &quot;
lcSQL = lcSQL & &quot;HAVING tbl_Report_Notifier.REPORT_TIME='&quot; & lcTime & &quot;';&quot;
Set rsReports = ThisDB.OpenRecordset(lcSQL, dbOpenSnapshot)

If rsReports.RecordCount <> 0 Then
'Loop Through List of Reports for Designated Time (0800, 1600 or 1630)
lcObjectType = &quot;acSendQuery&quot;
lcFormatType = &quot;acFormatXLS&quot;
rsReports.MoveFirst
Do While Not rsReports.EOF
lcReportID = rsReports(&quot;REPORT_ID&quot
lcMessage = rsReports(&quot;REPORT_Message&quot
lcSubject = &quot;Daily EMail Alert: &quot; & rsReports(&quot;REPORT_Name&quot
'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
&quot;IT Consultant & Web Master&quot;

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[BobJacksonNCI]

SB,

For question 2, you just need to put a criteria in the query that feeds the report:

< Date() - 31

to select records over 31 days old.


Your first question requires a lot more...

First, you must be able to set up the database so the report runs from a macro. Let's say you name the macro mcrRpt.

Create a .BAT file that opens the database and fires the macro. In a .BAT file you have to provide full paths to the Access program and to the database. E.G.,

c:\progra~1\micros~1\office\msaccess.exe &quot;m:\shareall\dbases\Inbound Call Volume.mdb&quot; /x mcrRpt

To get the short form of a directory/folder name that is required for the path to Access, get to a prompt and play with DIR /X

/X shows you the full and abbreviated name.

Test it - make sure that double clicking the .BAT file from a command (DOS) prompt results in the report.

Then, you need to verify you have task scheduler installed or install it. Double click &quot;My Computer&quot;. If you see an icon that says &quot;Scheduled Tasks&quot;, you can continue. Otherwise you'll need to install it from your Windows 98 CD.

Once installed, create a scheduled task to execute the .BAT
To get instructions on this, double click the task icon and select help. Note there is a &quot;Add Scheduled Task&quot; button in the file list that triggers a wizard. Still, I recommend reading the help first.

Hopefully this is enough for you to follow through on.


HTH,
Bob
Your mileage may vary, but following the guidelines in faq181-2886 will help you reach your goal.

 

[NorthNone]

Bob, if the database has user level security, is there a way to give the .bat file the username and password to use?
TIA
JSouth

 

[BobJacksonNCI]

JSouth,

If you use Task Scheduler to run the .bat file, access permissions will be established by the scheduled task. I.E., the PC that runs the Task Scheduler must be left on and Task Scheduler uses the login name and password from the PC. Also, each task has a password that must agree with the logged in user's password. When you change your password you must update the passwords in the tasks or they will not run.

HTH,
Bob

 

[NorthNone]

Thanks Bob. That sounds like Windows/Network security. What I'm struggling with is security that resides in the Microsoft Access database itself: User-Level Security where you set users up solely for that particular database with particular permissions for tables/queries/forms etc... Those logins and passwords are different from the ones used to log into my computer.

 

[BobJacksonNCI]

You're welcome.

Although workgroup security is used in my shop, it's not required for the databases that use the Task Scheduler. So, I don't have experience with that. And I don't know the answer.

Alternatively, if you can leave Access running on a PC, you could use Steve's logic above.

Good Luck!