Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Access as a Task Scheduler

Posted on 2013-11-07
11
395 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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
 
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 46

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

791 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