• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

Automaticaly run a report in MS Access

I would like to run a report on the 16th and the 1st of each month, and have it run automatically in access.

Right now i have the following that runs a report every night at midnight, it updates a temp table then runs a report:

Private Sub Form_Timer()
    If Time() = CDate("12:00am") Then
        Call UpdateTempTables
    End If
End Sub

I'm wondering is there a way along this same line to run a report on the 16th and 1st of every month?


0
joe_GDB
Asked:
joe_GDB
4 Solutions
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Joe,

  Rather then doing this within Access, your better off to call Access with a command line switch (either /x, which calls a macro or /cmd, which passes command line arguments) and use the Windows task scheduler to do the scheduling.

 JimD.
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
I prefer to use the Windwos Tash schudle to open a datasb that runs and closes auatomatically without any user interaction.

Even with your example I would have it run as a scheduled task every night at midnight.  I have found that having a form opened all the time with a timer event has many issues beside not being very good use of system resources.
0
 
joe_GDBAuthor Commented:
thanks for the quick response.  I like the Task Scheduler idea but how would i set that up to open the specific database and then "Call UpdateTempTables"
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Call UpdateTempTables>>

Number of ways:

1. That's the only thing the DB does, either by it's Autoexec macro or a startup form.

2. Create a macro that executes that and call it from Task Scheduler using /x <macro name>

"C:\Program Files\O2000\Office\MSACCESS.EXE"  "C:\Program Files\Custom\OrdMon.MDB" /X MyMacro

3. Add logic to look at the cmd line argument passed using the Command() function and pass an argument on the command line with /cmd.

Looks something like this in Task Scheduler:

"C:\Program Files\O2000\Office\MSACCESS.EXE"  "C:\Program Files\Custom\OrdMon.MDB" /CMD NOALERTS * *

  I have code for parsing the command line option if you want to using multiple pass parameters like the command above.

JimD.
0
 
Jeffrey CoachmanMIS LiasonCommented:
As any expert will tell you, there are always "Timing" issue with doing things like this:

What if the code that updates the tables fails?
What if the code that updates the tables does not run for any reason?
What if the updates the tables runs twice, by accident
What i the computer is turned off
What if the user is logged off?
What if the computer is asleep or in hibernate mode
...etc

Also remember, in most cases you need to run a scheduled task as an administrator.


Now, none of these things may even be an issue here, ...it's just that anything that runs "un-attended" always needs rock solid validations, Rollbacks, ...etc


;-)

JeffCoachman
0
 
Nick67Commented:
The caveats you have seen.
If the report runs regularly at midnight, and you don't have problems with that then you can extend that.
TimerInterval sets how frequently the form does the timer action
It's measured in milliseconds.
You may want to mess with that to cut down on the resources the open app uses

Private Sub Form_Timer()
    If Time() = CDate("12:00am") Then
        Call UpdateTempTables
    End If

    if Day(Now()) = 1 or Day(Now()) = 16 then
        Call SomeSubThatRunsYourReportFullyAutomaticallyAndLogsSuccess
    end if

End Sub

0
 
Nick67Commented:
The TimerInterval and logging success become important because on the 1st and 16th, the form is going to attempt that sub once every 'TimerInterval' milliseconds for the full 24 hrs.  You could take the TimerInterval to 300000 - five minutes - or longer.  What becomes critical is how crucial it is for the other report to run at EXACTLY midnight, because if you open the form at 4:25, say, and set the interval for an hour, things will happen at 12:25.

It all depends on how you want to tackle it. :)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now