Task Scheduler (1 Viewer)

[ProgramRasta]

Hello All

I have a question which I’m sure has been asked a million time’s before. However I am unable to find an appropriate solution.

I am looking to schedule recurring tasks for the year, some are weekly, monthly, annual. Of these tasks some start on the 1st of month and others say the 20th.

Any help is appreciated.
Rasta

 

[isladogs]

Use the Task Scheduler wizard and adapt the settings for each task.
It can easily manage the examples you've just given

 

[The_Doc_Man]

When you say "Schedule tasks" ... do you mean actually executing a task or merely reminding someone?

If all you want is a reminder then look into using Outlook, which has a pretty good task reminder system.

If you really meant executing tasks, then Isladogs (Colin) gave you the right advice.

 

[ProgramRasta]

Thank you for taking the time to respond.
I have say 50 tasks, some are monthly, daily, weekly, some start on the 12th, 21st etc. I need to run a database query that shows me which tasks are due in the current month.

I have the task list and when they are due, I’m just wondering how I could schedule them for the year and my database query to pick them up.

Thanks

 

[isladogs]

As already stated use the Task Scheduler wizard.
There are options for any required frequency,
What isn't clear from your posts is what the task entails.
If, for example, you need to run a query at a specified date/time, you should create a macro to run that query,
You will then need to schedule the task to open your app and run the macro using the /x switch
For more details on that, see https://support.office.com/en-us/ar...4EF5-4178-B235-441737DEB3A6#ID0EAABAAA=Access

Once you've done that successfully for one task, repeat the same idea for each of your other tasks
The attached guidance that I supplied to one of my clients may be useful to get you started.

 

[Micron]

This sounds like a question on how to query a table to get a list of upcoming tasks, not how to schedule running of queries - but I've been wrong at least once this year and it's only February

If that's what this is about, then you feed the query a date or number of days to look in the future and get either
- last performed date + frequency IF it falls within the date parameters, or
- next due date, which means that value is stored, which means calculated values are being stored when they shouldn't, or
- this

how I could schedule them for the year

means you want an Outlook sort of calendar within your db. I'm not sure the requirement has been clearly expressed.

 

[Dreamweaver]

I'm with @Micron as he asked about querying a database

 

[The_Doc_Man]

Here is the stumbling block. You may certainly build something that would act as a scheduling agent within Access. It is tedious but not at all impossible. However, you have two better avenues for this requirement outside of Access.

For messages, you can't beat the Outlook Task/To-Do system, a wheel that has already been invented. It was designed to be running in background mode so that you would be able to respond to messages quickly. It runs for long periods with no particular problems. It can ping you with a reminder to do your reports and it works even on yearly events.

The problem with running this hypothetical event scheduler through Access is that to be effective, Access will have to be running all of the time. Heck, that isn't an Earth-shaking requirement, but the Windows Task Manager is already there as part of Windows, which WOULD be running all of the time. And the Windows Task Scheduler is another wheel that has already been invented.

If you really, honestly, viscerally have to have this done in Access, my question would be "Why?" Having said that, it is certainly possible but there are some "gotchas" to consider. I once wrote something similar to this for OpenVMS because they didn't have that kind of scheduler. I.e. they did not have the equivalent of a Windows Task Scheduler or a UNIX Cron task. But be warned, it is tedious as all heck.

 

[isladogs]

Doc has 'hit the nail on the head'. You can indeed run a series of queries or reports etc at specified times from within Access but to do so requires the Access app to be running 24/7 and have one or more timer events always running in the background.
For tasks that run to be run frequently e.g. every hour, that would probably be the best solution.
However, for tasks that need to run less frequently e.g. Every Monday at 09:00, it would be inefficient to manage this from Access.
That's where using Task Scheduler is ideal.

For example, in my schools apps I have a scheduled task to run an Access app every weekday at 02:00 to import, process and log the latest school, staff and student data from an external source. The process takes 30-45 minutes to complete and is done remotely when the network is 'quiet'. Once completed, the Access app is automatically closed
Another scheduled task runs twice each weekday at 09:15 & 14:15 to import the latest student attendance data after each registration session.

 

[ebs17]

The problem with running this hypothetical event scheduler through Access is that to be effective, Access will have to be running all of the time.

You could also say that the windows task scheduler only has to check whether the database application was opened daily. A table access to a table that logs activities would suffice.
Starting the database once when required is certainly less time-consuming than starting one per task.

Task monitoring can be managed in the database. It could be that tasks are not to be carried out strictly on a date, but on a (following) working day. Controlling this should be easier via the database.

Additional thought: The planning and maintenance of tasks should also be easy to maintain by a normal user. Documentation of the actual execution will often also be interesting.

Where can you do that better than in the database?

Eberhard

 

[ProgramRasta]

Thanks all for the replies.

I didn’t explain myself too well.

I have 50 stock orders that are due on business days throughout the year. They are due at different times. What I was looking for is vba code that will add the entries into a table based on dates.

for example, if I have 1 purchase order each month for product x that is due on the first of the month,I can add the 12 entries in a database table for the year. Some entries are every 2 weeks, day on the 4th of the month.

I was just looking for the most efficient way to add these entries for the year. I don’t need to automate anything. It’s merely just to have the data in the table. Would case statements be the way to go?

thank you
Rasta

 

[ebs17]

You could orientate yourself to something like that:

SELECT
   Task_ID,
   DateAdd(D.Intervall, D.Frequency * T.I, D.StartDate) AS planned_appointment
FROM
   tblData AS D,
   T999 AS T
WHERE
   DateAdd(D.Intervall, D.Frequency * T.I, D.StartDate) BETWEEN [Start_period] AND [End_period]

T999 would be a simple table that only contains a field I (PrimaryKey) with the consecutive numbers 0 to 999.

Structure of tblData

Task_ID	StartDate	Intervall	Frequency
2	1/1/2020	m	1
3	1/1/2020	ww	2

Interval and frequency are based on the arguments for the DateAdd function.
The query shown can be used as the basis for an append query, but can already be used as such.

 

[The_Doc_Man]

When I did something like this, I used date string portions. My entries looked similar to this:

EDate  EMonth EName
1      *      RUN MONTHLY_DISK_CLEANUP
15     Apr    RUN ANNUAL_INCOME_TAX_FILER
20     Jan     RUN BIMONTHLY_USER_SWEEP
20     Mar    RUN BIMONTHLY_USER_SWEEP
20     May    RUN BIMONTHLY_USER_SWEEP
20     Jul      RUN BIMONTHLY_USER_SWEEP
....

Then every scheduling period (for you, that would be daily) I would take the date and pull it apart with the equivalent of a DATEPART function to get NOWDAY and NOWMONTH. I would then do the equivalent of

SELECT ENAME FROM ELIST WHERE ( EDATE = NOWDAY ) AND ( ( EMONTH = NOWMONTH ) OR ( EMONTH = "*" ) ) ;

Of course, you would need to substitute or externally reference NOWDAY and NOWMONTH.

This is the SHORTENED version of what I actually ran because my scheduler also could do multiple items at specific times per day and its checkup period was 15 minutes. In what I showed you, you have to repeat entries if a wild card won't work, like my "every two months" example. I actually used different syntax because I wasn't using SQL to do this but that would only muddle the waters to tell you what I actually used. The syntax I used allowed me to show everything on a single line instead of repeating the item for each iteration. But it is inappropriate for an Access forum.

Since you say you are not automating things, you would run the query manually when you were ready to use it. You could adapt that query to become the "back half" of an "INSERT INTO StockPurchases (field list) SELECT ..." query to actually do the inserts. In that case, you would select the name but supply constants for the date and other data that might be relevant.

 

[ProgramRasta]

Thank you for the replies.