Access as a Task Scheduler

<<The key is having a form with a timerinterval and code in the Timer event that will assess the current time and potentially other criteria (does a file or table exist, does a date/time field in a particular table contain data for a specific data, ...) and then perform one or more tasks based on those criteria. >>

  I've done scheduling like that as well and I avoid that now.   I find it's better to have an app that does it job and terminates, which gives you a clean slate each time.

  When you leave an Access app running 24 x 7 and have it run the tasks, any mistakes on your part will lead to problems.  Also, all the functions that it needs to carry out need to reside in it (or you end up doing a lot of work), which makes things tend to get complex.

  So I've gone with the approach of having a handful of apps that each do a specific thing (process orders, import, export, monitor system operations, etc) and are kicked off by something externally.  They do their job and then quit.

 It is another approach though and if kept small and focused, it does work.  I had a time clock poller that was setup like this and it stayed running for months on end without issue.

<<Care to expand on your comment: "which ultimately I found out was not really my problem">>

 I had the same issue when I moved a client from Windows Server 2003 to 2008 and even under 2003, I had issues.   But the move to 2008 made them far worse.

  I would get Access apps that would hang when run from task scheduler, but be perfectly fine when run manually.

  I tried all kinds of things; every possible setting in task scheduler, running under different accounts, running from a batch file, setting an exit code, etc.  Nothing helped.  So I wrote the scheduler.  Things ran a lot better, however I was stunned when three days later, I had an app that hung.

 What I realized finially after weeks of waiting for hangs and debugging was that it was objects I had not cleaned up causing a problem.  Setting them to nothing took care of most of it.

 But the reall kicker was Application.Quit.    I switched to DoCmd.Quit, and 90% of the problems I had disappeared.

 Even so, I've stuck with my scheduler becase task scheduler on occasion will still act quirky.  However I still don't believe the issue is Task Scheduler itself, just the method it uses to run tasks.

 I have noticed often that MSACCESS.EXE will exit with a memory access violation error, so there's some type of bug in Access when it goes to shutdown.   I'm not sure what the difference is between DoCmd.Quit and Application.Quit, but there is a difference and it's some how related to this.
 
The other issue I found is that Task Scheduler usings a CreateProcess API call, where my scheduler simply uses Shell().

As a result, I have a lot less problems, but still on occasion will get:

"out of memory"
"workgroup file cannot be read"
"Administrator has placed the database in exclusive mode"

 none of which is true and there is nothing wrong.  Close the error, fire off the task again and it works flawless.

  So I'm convinced that the problems really lie with Access and not the task scheduler.

Jim.

Jim,

Thanks for the feedback.  My example above was just to show a very simple version of what a scheduler might look like.

By your comments, I assume then that your scheduler kicks off these smaller, task specific Access applications using Shell() with command line parameters which are then evaluated in each of those smaller applications.

<<By your comments, I assume then that your scheduler kicks off these smaller, task specific Access applications using Shell() with command line parameters which are then evaluated in each of those smaller applications. >>

  Yes.   Of course there's not a "one size fits all" approach here.  In some cases, it might make sense to have one scheduler handle all the tasks with the tasks built-in, or it may make sense to have one app for each individual task and have them kicked off externally.

  Really depends on what your doing and what makes sense.

  A scheduler that stays running all the time and executes tasks internally would make sense if there were a lot of startup/shutdown overhead for example.

 In general though, I've found over the years that a series of small focused apps scheduled externally is the most flexible and robust and easier to maintain.

 That still not saying it's the solution for everyone though.  It's just what has worked for me given the situations I've had.

Jim.

FWIW: I've found .NET to be much better suited for these types of things. The builtin FileWatcher class is tailor-made for these things, without a tremendous amount of work. IMO if you need something like this, it's worth knocking around in .NET just for this feature alone.

But if you're looking to do this entirely in Access, then Jim and Dale have you well in hand.

I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.

This question was answered:

https://www.experts-exchange.com/questions/28288197/Access-as-a-Task-Scheduler.html?anchorAnswerId=39631148#a39631148
https://www.experts-exchange.com/questions/28288197/Access-as-a-Task-Scheduler.html?anchorAnswerId=39631172#a39631172
https://www.experts-exchange.com/questions/28288197/Access-as-a-Task-Scheduler.html?anchorAnswerId=39631285#a39631285

Equal split among all 3. First comment is JimD's overall explanation, and 2nd and 3rd are JimD and Dale going into more detail.