Running a report automatically on a monthly basis

[MHUK]

Hi

Please can someone help me? Is it possible to have an Access 97 report run automatically on a regular (user-defined) basis?

I have already created the report, but was wondering if there was an automatic way of actually running it on a particular date and time? Is there some kind of MS DOS batch file I can create to enable this?

Thank you for any help.

MHUK

 

[mcfaddea]

Hi
Easiest way to do this is to set it up under the standard windows scheduler

cheers
Alan

 

[MHUK]

Thanks Alan, for your reply. How would I go about doing this with the Window's Scheduler? I'm on Windows 98. How would I link the database and report to the windows scheduler?

Thanks for your help.

Mhuk

 

[MHUK]

Please can someone help me if possible. I am trying to run a report automatically on a monthly basis in Access. Does anyone know if Windows Scheduler would enable this?

Thank you for any help.

 

[Aubs010]

I would have thought that you use the access database file as the scheduled item in the windows scheduler and then set the database so when it opens, the first thing to open is the report.

Something like in the switchboard, have an on OPEN event to call the report
Aubs

 

[BobJacksonNCI]

MHUK,

I have several scheduled tasks that perform automated file copies, so I know about this. However, you might not want users playing with the task scheduler. For example, if your users want the report to run every Monday morning at 7:30 AM, task scheduler would be great. If they want it on a varying date or time every week, that would require visiting the task scheduler - something you might NOT want your users doing. So, besides giving an overview on the scheduler, solution number two would start you off on a method to trigger the report inside Access. To use that, the database would have to be open during working hours. Hope your users have enough memory installed!

Please note that quite a bit of legwork is required on your part for either solution. If you have the time and patience, it will increase your knowledge. (At least it certainly did mine.) If you are short on either, you might want to go outside this forum and work with someone one-on-one. Afraid that can't be me as I have Labrador Retriever puppies due and will be busy!

Two solutions to choose from:

1. Using the task scheduler.

First, you must be able to set up the database so the report runs automatically. I.E., double-clicking the .MDB file opens the database and an autoexec macro (auto starts - think autoexec.bat) - runs the report.
Create a .BAT file that does nothing more than open the database. 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 "My Computer". If you see an icon that says "Scheduled Tasks", 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 "Add Scheduled Task" 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.

The second solution comes from MS Knowledgebase solution number 158937. It's purpose is to show how to automatically perform an automatic compaction at a scheduled time. So, some of the code isn't suited to your purpose. And the following is a cut-and-paste. You have to figure out where changes are needed.
Hint: To allow user defined basis, add a date/time field to the form and allow users to set it. The logic to determine if it's time to run the report will be a little tricky. You should think through the possibilities you want to provide. Then you need to set a flag to indicate the report has been run. And the complexity increases if they want to schedule multiple runs in advance.

KNOWLEDGEBASE 158937 EXERPT FOLLOWS=====================
Create a new blank form and set the following properties:
Caption: Compact Databases
Default View: Single Form
Scrollbars: Neither
RecordSelectors: No
NavigationButtons: No
OnTimer: [Event Procedure]
TimerInterval: 60000 '60000 milliseconds=1 minute

Click the Build button next to the OnTimer property of the form and type the following procedure:
In Microsoft Access 7.0 and 97:
Private Sub Form_Timer()
'=====================================================
'The Timer event runs this code every minute. It compares your system time with the StartTime variable. When they match, it begins compacting all databases in the DBNames table.
'===========================================================
Dim StartTime As String
' Set this variable for the time you want compacting to begin.

StartTime = "12:00 AM"

' If StartTime is now, open the DBNames table and start compacting
If Format(Now(), "medium time&quot = Format(StartTime, _
"medium time&quot Then
Dim RS As Recordset, DB As DATABASE
Dim NewDBName As String, DBName As String
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("DBNames&quot
On Error Resume Next
RS.MoveFirst
Do Until RS.EOF
DBName = RS("DBFolder&quot & "\" & RS("DBName&quot
' Create a new name for the compacted database.
' This example uses the old name plus the current date.
NewDbName = Left(DbName, Len(DbName) - 4)
NewDbName = NewDbName & " " & Format(Date, "MMDDYY&quot & ".mdb"
DBEngine.CompactDatabase DBName, NewDBName
RS.MoveNext
Loop
' Close the form, and then close Microsoft Access
DoCmd.Close acForm, "CompactDB", acSaveYes
RS.Close
DoCmd.Quit acSaveYes
End If
End Sub
======================================================
END OF KNOWLEDGEBASE 158937 EXERPT

Like I said, a LOT of legwork either way.
There are several of us using Access where I work. BTW, I'm an antique mainframer. We collectively marvel at what IS possible using Access. I, however, also can't conceive how some seemingly simple tasks are so difficult...

Hope this helped even a little!
Bob (M.E.A.N.)
Moderately Exasperated Access Novice ;-)

 

[BennyWong]

Hello MHUK,
How about spending $34.95 to solve your problem? Here is the website that may help?

http://www.acc-technology.com/scwiz.htm

Here is some details about the product.

SCHEDULE THE RUNNING OF YOUR MS ACCESS TASKS!

The Schedule Wizard will allow you to automate the process of periodically running your Microsoft Access actions such as running a Macro, opening a Query or printing a Report or Form.

With the Schedule Wizard, you can run any number of actions on an hourly, daily, weekly or monthly basis, then after the action has been executed the Wizard will automatically update to when to execute the action next again (i.e. tomorrow, next week, or next month). This can be set-up to suit your updating/reporting cycles or ad-hoc. In addition, you can also specify to only run your actions on a Weekday only basis (Monday-Friday).

In many businesses a lot of precious time and staffing resources can be wasted on performing the mundane task of periodically running Microsoft Access actions and waiting for these actions to be completed. You can use the Schedule Wizard to be the "Trained Monkey" of your office, set-up the date and time you want your Microsoft Access actions to be run, then let the Schedule Wizard do the rest!

HOPE THIS HELPS!
Benny Wong