Solved

Automaticaly run a report in MS Access

Posted on 2011-03-21
9
248 Views
Last Modified: 2012-08-13
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
Comment
Question by:joe_GDB
9 Comments
 
LVL 57
ID: 35183642
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
 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 125 total points
ID: 35183649
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
 

Author Comment

by:joe_GDB
ID: 35183774
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
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 125 total points
ID: 35183848
<<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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 125 total points
ID: 35185119
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
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 125 total points
ID: 35266247
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
 
LVL 26

Expert Comment

by:Nick67
ID: 35266413
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36260793
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now