Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 405
  • Last Modified:

Access as a Task Scheduler

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
Dominator1025
Asked:
Dominator1025
  • 4
  • 2
  • 2
  • +1
3 Solutions
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
Dale FyeCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
Dale FyeCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Martin LissRetired ProgrammerCommented:
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now