Solved

Timed Triggered Reports

Posted on 2003-11-13
8
341 Views
Last Modified: 2006-11-17
I am looking for some guidance before I build some automated reports. I already know how I will output/distribute the reports(s), but I am trying to understand the best way to trigger the reports. Basically I want to trigger a couple of reports to be sent out say at 6 pm every weekday night.

My db has a BE and multiple FE's distributed out. I first thought i could have a code to execute on the Current Event for the Main Form of the db to say such in the below example, but I thought of two scenarios that might not work with this idea. 1) Anyone who had the db Open when then execute possible multiple e-mails that are sent out and also I would have to ensure to keep a db Open to at least execute the code at the set time.

My idea is to create a separate FE basically to run the automated reports and keep it open at all times.

Any suggestions or different approaches to this scenario... so basically I just need a way to execute a timed event every weekday.


Example:

     If Time = 6:00 pm Then
          DoCmd.Output..... <- Output Reports
          SendEmail <--- A Sub to distribute attached Reports to users.
    End IF
0
Comment
Question by:snoble
  • 4
  • 3
8 Comments
 
LVL 2

Assisted Solution

by:Team929
Team929 earned 50 total points
ID: 9744764
Ever thought about making it a scheduled task?
0
 
LVL 1

Author Comment

by:snoble
ID: 9744891
not familiar with scheduled task.... maybe lack of experience. Please explain. Is it apart of Access or a computer config?
0
 
LVL 2

Accepted Solution

by:
glennkerr earned 75 total points
ID: 9745115
Scheduled tasks can be created to run at preset times.

Create a front end that runs all your reports when it opens, then closes.

Got depending on your OS, it will be...
Win98 - Start | Settings |Scheduled tasks
WinXP - Control Panel (classic view) or Control panel | Performance & Maintenance (normal view)

Add a new task.  You'll need to click the browse button to find your database.
Set the time and frequency.

As for your code above, if you plan to do it that way, you should do something more like...

If hour(now()) > 18 and not bHasRun then
     .... code here
    bHasRun = True
End if

The chances of your code executing that line at EXACTLY 6:00:00 PM might be slim.
(especially if another task took the CPU time away for a moment)
0
 
LVL 1

Author Comment

by:snoble
ID: 9745132
I appreciate the code... and I figured out the scheduled tasks but since the db is on a Server and the Network Admin is a dickhead, I am sure I would have littled or no access to play with the scheduled tasks. Appreciate the advice... I will just create another FE that is always open to manage the reports.

Also... what is the bHasRun code? I am still a novice, and would like to know how to work that one. Thanx all....

Steve OUT!
0
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

 
LVL 2

Expert Comment

by:glennkerr
ID: 9745156
bHasRun is just a variable that you declare in your routine.

Dim bHasRun as boolean

bHasRun = false

if ...


You don't need to set the scheduled task on the server.  Make a front end that does one thing - run your reports when it opens, then it closes.

Put the front end on any machine you have access to.

Make the sched task on that machine - voila!
0
 
LVL 1

Author Comment

by:snoble
ID: 9745182
Woud like to... but we are through Terminal Services only and the bastard even locks out the right click on the damn mouse! No access to even the control panel. Will see if he would "bless" me with the permissions.

Also as a note... would I have to be logged on to have the scheduled task execute?

Also... thought the bHasRun was some cool code I hadn't seen yet. Thanks

Steve OUT!
0
 
LVL 2

Expert Comment

by:glennkerr
ID: 9745196
Yeah, you would have to be logged in for it to run.

If your Admin leaves the server logged in all the time on a particular account, that would be the account to set it up on.

Maybe he might take some comfort knowing that your program will terminate as soon as it's finished - leaving the server unencombered with more processes.
0
 
LVL 1

Author Comment

by:snoble
ID: 9745204
Thanx for the info.... Have a good one.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

762 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

23 Experts available now in Live!

Get 1:1 Help Now