Solved

Automaticaly run a report in MS Access

Posted on 2011-03-21
9
287 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 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
SendBlaster Pro 4 - Bulk Email Sending Software

SendBlaster 4 Pro - Best Bulk Emailing Sending Software
Automatic Subscribe / Unsubscribe Processing
Great for Newsletters & Mass Mailings
Optional HTML & Text Composition
Integration with Google Features
Built in Spam Score Checking
Free Professional Templates - Feature Packed!

 
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
 
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 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

Create Professional Looking Email Signatures

Create "Professional HTML Email Signatures" with ease.
7 Day Money Back Guarantee if not 100% Satisfied.
Affordable - Try it out for 7 Days Totally Risk Free.
Installers provided for over 45 Email clients.
Both Windows & MAC Supported.
Highly Recommended!

Question has a verified solution.

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

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

734 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