Solved

Access as a Task Scheduler

Posted on 2013-11-07
11
392 Views
Last Modified: 2013-12-03
I have been looking for solutions to help with improving efficiencies in my workgroup. We have about 45 access databases that get kicked off via Windows Task Scheduler on a daily basis. I would like to try incorporating more rules as to how they kickoff and what they do after kicking off. I am looking at rules that would be based on file modified dates and waiting for files to be updated before moving on to the next database.

I came across this topic and was wondering if Jeff D or anyone else could share their DB that is a Task Scheduler? This sounds like a good solution.

Thanks!


http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_28278726.html
0
Comment
Question by:Dominator1025
  • 4
  • 2
  • 2
  • +1
11 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 334 total points
ID: 39631148
<<I would like to try incorporating more rules as to how they kickoff and what they do after kicking off. >>

I do two things in this regard:

1. Pass in arguments as to how the application should act.

2. Anything beyond simply being kicked off at a certain date/time is done in the app (i.e. checking for received files).

 What you'll find is that writing a good scheduler is a lot of work.  Certainly you can write a lot of things into it; checking for received files, startup/shutdown, login/logoff, etc, but there are other ways to accomplish those tasks.

  There are however schedulers out there that will do all that, some are reasonable priced, but there's one that runs around $10K.

  But I like to keep things simple and to me, functionality should be kept in one place.   If for example I have a DB that imports a file into a database, then checking if there is a file to import is part of that task anyway, so why not just fire off the app frequently, check, and quit if no file is found?

 So with that in mind I do the two things above.  For #1, I always pass in arguments using the /CMD switch.    This allows me to pass in multiple arguments and react accordingly.  

 For example, if I have an app that does various exports, I might want to do them one at a time or do them all.    So the first argument might be "Export Task ID" and I either pass in an identifier for the Export task or * to indicate all.  

 For that, I have code that I can share.

 However the task scheduler I posted in the other thread was developed specifically for a client and they own it, so I can't share it.   But it doesn't do anything more then Windows task scheduler and actually, does a lot less.  It's only job is to kick off an app at a certain day/time, repeat for a number of times, x minutes apart, and do that until a certain time.

 The only reason I wrote it was so that I could get around certain issues with the Windows Task Scheduler, which ultimately I found out was not really my problem.  It is nice though to stay in control, so I've kept on using it.

Jim.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 334 total points
ID: 39631172
So here's the code to help with the /CMD switch.  Note that these won't run as is because you don't have my error handler, but outside of that it will work as is.

Use GetCommandLine() when your app starts up to read in the arguments passed.

Then, anywhere in the app, use GetCommandLineArg(nth number argument you want), to read the argument value.

an example of what you would put in task scheduler might be:

"C:\Program Files\O2000\MSACCESS.EXE" "C:\JOBS\ORDERIMPORT.MDB" /CMD  *  NOALERTS

So your passing two arguments, first is the import job to do, * being for all, and NOALERTS here is to tell the app to run in "silent mode" and not send out any e-mail alerts.

By doing that, I can define another task in scheduler like this:

"C:\Program Files\O2000\MSACCESS.EXE" "C:\JOBS\ORDERIMPORT.MDB" /CMD  * ALERTS

 and only run that during the hours when I want e-mails.

That should give you enough to go on and get started.

Have fun!

Jim.

Paste into the top of a standard module:

' Used within this module
Private gvarCmdArgArray() As Variant


Then paste these two routines into the same module:

Public Sub GetCommandLine(Optional intMaxArgs As Integer)

    Const RoutineName = "GetCommandLine"
    Const Version = "2.0"

    'Declare variables.
    Dim strChr As String
    Dim strCmdLine As String
    Dim strCmdLnLen As Integer
    Dim intInArg As Integer
    Dim intI As Integer
    Dim intNumArgs As Integer

    'See if intMaxArgs was provided.
10  If IsMissing(intMaxArgs) Then intMaxArgs = 10

    'Make array of the correct size.
20  ReDim gvarCmdArgArray(intMaxArgs - 1)
30  intNumArgs = 0
40  intInArg = False

    'Get command line arguments.
50  strCmdLine = Command()
60  strCmdLnLen = Len(strCmdLine)

    'Go thru command line one character at a time.
70  For intI = 1 To strCmdLnLen
80      strChr = Mid(strCmdLine, intI, 1)

        'Test for space or tab.
90      If (strChr <> " " And strChr <> vbTab) Then
            'Neither space nor tab.
            'Test if already in argument.
100         If Not intInArg Then
                'New argument begins.
                'Test for too many arguments.
110             If intNumArgs = intMaxArgs Then Exit For
120             intNumArgs = intNumArgs + 1
130             intInArg = True
140         End If
            'Add character to current argument.
150         gvarCmdArgArray(intNumArgs - 1) = gvarCmdArgArray(intNumArgs - 1) + strChr
160     Else
            'Found a space or tab.
            'Set intInArg flag to False.
170         intInArg = False
180     End If
190 Next intI

    ' Don't want to do this as some arguments may be optional.
    'Resize array just enough to hold arguments.
    'ReDim Preserve gvarCmdArgArray(intNumArgs - 1)

End Sub



Public Function GetCommandLineArg(intArgNumber) As Variant

    ' Returns an argument from the command line
    ' Null is returned on Error or non-existant argument

    Const RoutineName = "GetCommandLineArg"
    Const Version = "2.0"

10  On Error GoTo GetCommandLineArgError

20  If intArgNumber > UBound(gvarCmdArgArray()) Then
30      GetCommandLineArg = Null
40  Else
50      GetCommandLineArg = gvarCmdArgArray(intArgNumber)
60  End If

GetCommandLineArgExit:
70  On Error Resume Next

80  Exit Function

GetCommandLineArgError:
90  UnexpectedError ModuleName, RoutineName, Version, Err.Number, Err.Description, Err.Source, VBA.Erl
100 GetCommandLineArg = Null
110 Resume GetCommandLineArgExit

End Function
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 166 total points
ID: 39631285
Dominator,

As Jim mentioned you can build this as simple or complex as you want.  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.  Simplest case might look like:
Private Sub Form_Timer()

    Dim varPrevious As Variant
    Dim strTo As String, strSubject As String, strMsg As String
    
    On Error GoTo ProcError
    
    If Nz(varPrevious, #1/1/2013#) <> Date Then
    
        varPrevious = Date
        CurrentDb.QueryDefs("QueryName").Execute dbFailOnError
        
    End If
    
    Exit Sub
    
ProcError:
    strTo = "youremail@xxx.yyy"
    strSubject = "Scheduled task #1 failed"
    strMsg = "Err #: " & Err.Number & vbCrLf _
           & "Err Desc: " & Err.Description
    DoCmd.SendObject acSendNoObject, , , strTo, , , strSubject, strMsg
    
End Sub

Open in new window

Then set the TimerInterval to some large value (60,000 = 1 minute), frequently use 360,000 for once every hour.

Jim,

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

I've encountered a number of problems with task scheduler not kicking off batch files on a clients VM.  I am able to run these batch files (they kick-off Access reporting applications) manually and with task scheduler on my PC and from other PCs on my clients sight, but when run from the task scheduler on the VM they will not run and generate no error messages.  It may be that I'm encountering the same 'not really my problem'.

Dale
0
 
LVL 57
ID: 39631496
<<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.
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 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39631551
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.
0
 
LVL 57
ID: 39631733
<<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.
0
 
LVL 84
ID: 39632148
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.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39689545
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
LVL 84
ID: 39689547
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

743 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

11 Experts available now in Live!

Get 1:1 Help Now