Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Automaticaly run a report in MS Access

Posted on 2011-03-21
9
Medium Priority
?
295 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 58
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 500 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 500 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 500 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 143

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

597 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