Making a Task Scheduler in Access 2010

[InstructionWhich7142]

There are some reports/queries I need to export and email at certain times, I've got the email part sorted but my "Certain Times" code needs significant improvement,

I'd like to select specific times/reoccurances for each "report" and store them in a table like Windows Task Scheduler / Linux's Cron, I could start from scratch figuring out how to do things like start at X time, repeat for 8 hours, or "Run if missed" based on the timestamp recorded last time it was run, however I feel like someone's probably done this already and wondered if any of you had seen an example in the style of Northwind,

Thanks

edit (I've tried google but because of the search terms I get guides of how to run Access from Windows Task Scheduler)

 

[vba_php]

perhaps you are using the wrong search terms. try this instead?

https://www.google.com/search?q=vba+run+scheduled+task

 

[isladogs]

As every scheduled task is unique its impossible to provide a template.
However perhaps the attached advice that I wrote for one of my clients will help get you started

Also have a look at the similar threads below and use the forum's advanced search feature

 

[InstructionWhich7142]

I think I didn't word my question brilliantly, I basically want a query to identify which lines in a table are due based on some fields of like day, hour, repetition

 

[isladogs]

That's certainly not what I thought you were asking from the title and before you added the edit line.

You'll need to be more specific about the queries you need for someone to give a meaningful answer.

 

[vba_php]

I think I didn't word my question brilliantly, I basically want a query to identify which lines in a table are due based on some fields of like day, hour, repetition

There was a similar question asked just like 2 days ago. Do you remember what thread that was Colin? I can't remember who gave the answer but it was solved. I can't remember the title of the thread either.

 

[InstructionWhich7142]

That's certainly not what I thought you were asking from the title and before you added the edit line.

You'll need to be more specific about the queries you need for someone to give a meaningful answer.

Apologies, it's difficult to get the wording right sometimes when it could be taken two ways and the other way is more commonly asked,

When i said "Making a Task Scheduler in Access 2010" I literally meant from scratch using VBA - instead of - windows task scheduler

 

[vba_php]

Chris,

I don't have the time to provide a sample right now as I'm coding on 2 different servers at the moment, so maybe isladogs can help out. but I believe this is usually done using a form that is bound to a table. so when you view a record through the form, you have fields like "last date sent" and "next scheduled send date" and "next scheduled send date missed?", to which the latter would be a boolean field I'm guessing? but at any rate, you might want to run an advanced search on this forum and search for "posts made by" and put the string "The_Doc_Man" in as the username. I think he was the one that answered this same type of question 2 days ago. I will keep a notification of new posts in this thread though just in case no one else can help you out.

 

[InstructionWhich7142]

Thanks for taking the time Adam, sounds like you're fairly flat out! I've found a post about "untick a check box after 48 hours" if that's the one you meant?

I sort of know where to start but some thought and trial and error is required before I make it a thing, just checking i'm not reinventing the wheel before I get in amongst it

 

[vba_php]

Thanks for taking the time Adam, sounds like you're fairly flat out! I've found a post about "untick a check box after 48 hours" if that's the one you meant?

I sort of know where to start but some thought and trial and error is required before I make it a thing, just checking i'm not reinventing the wheel before I get in amongst it

yeah I think that's the one. does Doc_Man have a post in that thread? if he does, then that's what I'm talking about.

 

[InstructionWhich7142]

Yea he does, about basically checking datefield1 > datefield2, that's what I currently do to check if a report has been run before emailing it as an attachment,

However it's the part where in Task Scheduler, where you set the future schedule, that I want to re-create, so initially both date fields are blank because it needs to run "every morning at 7am and again at lunch"

 

[isladogs]

You could do this by having a procedure that checks the time say every hour and if the time is equal to or just after the specified values, it then triggers another routine to run the code.
That's not difficult to do but it is wasteful of system resources as it means you have a timer running a background procedure all the time. Also if your app is closed the procedure won't run

One of my schools apps does something similar to import am & pm registration marks from an external database but it does this using task scheduler.
This is far more efficient & allows you to do other tasks whilst it is running

The idea is basically as follows:
1. Create a separate app which to fulfil this one purpose only. This is important as Access is a single-threaded application.
Link to any tables from your main app required for the process. Import any queries, forms, reports and procedure code also needed for the task.
Next create a macro to run your procedure and when done to quit your app - this could either be a named macro or an autoexec macro so it starts automatically when the app starts.
Close the app.
2. Create a scheduled task to runs twice daily at those specified times. The task needs to open Access to your new app. If not using an autoexec macro, use a command line switch to run your named macro
Make sure the task specifies that it will wake the computer if necessary and if you supply logon info, you won't even need to be logged on at the time

Hope that helps


.

 

[InstructionWhich7142]

I have about 150 different reports on 3 different frequencies currently but the way I did it back in 2011 is to manually set the IF's of like, "Day" between 2-6 "hour" between 8-17 every "half hour", or "first of the month" or "Monday 8-17", problem is where people want it on a tuesday I have to write a whole extra chunk of VBA if for working out if it's applicable,

It runs from a form timer every 30 minutes which I have running on a PC 24/7 (30 minutes is the current shortest frequency) however there's a new project that needs to check for records every 1-5 minutes at the most and update them, I'd also like an overnight schedule for something that can only run 10 at a time before exceeding the 2gb limit (sometimes 200+ records to process) etc etc

I'd like to base it on a table more like Task Scheduler where a few fields control the day and the start and stop time etc,

You could do this by having a procedure that checks the time say every hour and if the time is equal to or just after the specified values, it then triggers another routine to run the code.

How would you do this to run at 6:00 and 12:00 every week day? Because there's no value in a table today (27/11/2019 16:19) that says run next (28/11/2019 06:00) i'd need an query to insert that date or some VBA that just figures out that one needs running once it's after 06:00

 

[isladogs]

If you must do it using a timer event, then your procedure could include code like:

If CDbl(Time) = 0.25 Then '06:00
   'do something
ElseIf CDbl(Time) = 0.5 Then '12:00
  'do something else
End If

You would probably want to build in some flexibility as it won't check exactly at those times e.g.

If CDbl(Time) Between 0.247 And 0.253 Then '05:55 to 06:05
   'do something
ElseIf CDbl(Time) Between 0.497 And 0.503 Then '11:55 to 12:05
  'do something else
End If

However. if you have a timer event running continuously every 1-5 minutes, your app will probably be unusable for any other activity
A scheduled task may still be a better approach

Good luck

 

[InstructionWhich7142]

Yep that's pretty much what I have going on now, it's running on a dedicated PC so that's ok

Currently I'm trying to figure out how to implement similar in a query without 100s of criteria lines, i've got a boolean for each day but that's instantly resulted in 7 criteria, lol

 

[Solo712]

There are some reports/queries I need to export and email at certain times, I've got the email part sorted but my "Certain Times" code needs significant improvement,

I'd like to select specific times/reoccurances for each "report" and store them in a table like Windows Task Scheduler / Linux's Cron, I could start from scratch figuring out how to do things like start at X time, repeat for 8 hours, or "Run if missed" based on the timestamp recorded last time it was run, however I feel like someone's probably done this already and wondered if any of you had seen an example in the style of Northwind,

Thanks

edit (I've tried google but because of the search terms I get guides of how to run Access from Windows Task Scheduler)

I have a setup for auto backup that directly addresses this using a timer, and two values in a parameter table, 1) auto backup time interval, 2) time auto backup last executed.

When timer triggers the action i.e. last occurrence + interval >= Now(), it

1)disables the timer for the duration of the action,
2)executes the auto backup routine,
3)updates the backup last-executed value in the table,
4)re-enables the timer

You can have a number of events triggered this way, or create a task scheduler based on this scheme.

Best,
Jiri

 

[InstructionWhich7142]

That would be ok but you can get significant drift if a run is late etc and a good chunk of these things need to be sent of out for the start of the work day then as close to 12:00 as possible

oh and I've solved my 7 day issue with some binary:

daybin: clbincomp(-[clsun]+-[clmon]*2+-[cltue]*4+-[clwed]*8+-[clthu]*16+-[clfri]*32+-[clsat]*64,2^(Weekday(Now())-1))

clbincomp is just a vba "and" to do a bitwise comparison

the - are to deal with true being -1 and i'm a fan of doing 2^current week day for simplicity,

basically if the result of this is >0 then now() matches one of the required week days, saves me 6 critieria checking each week day tickbox for true/false

alternatively I could use a form to save the week combinations as binary