?
Solved

How to run Scheduled Tasks in the background

Posted on 2009-04-20
17
Medium Priority
?
1,196 Views
Last Modified: 2013-11-21
I had thought that my scheduled tasks where running in the background (without having to be logged into a session) until I discovered that the only reason they was working is because I was running them with Administrator credentials and the Administrator was being logged in on most mornings by the employees that change the backup tapes.
So, on those days or times when no one logs in as Administrator on the console the tasks do not run.

Is there a way to have them run without requiring a running session?

This is a Windows 2003 SBS in a Terminal Server environment.

In case it makes a difference, the Scheduled Tasks are Access forms that use a series of VB instructions to create reports and then output them to HTML format for Intranet reference by management and Internet mobile phone users.
They work fine when I execute them either manually or leave the Administrator logged in.
0
Comment
Question by:slamond
  • 11
  • 5
16 Comments
 
LVL 15

Expert Comment

by:tntmax
ID: 24188719
Setup another user account with backup privileges, then set the task to run even if no one is logged in.
0
 
LVL 15

Expert Comment

by:tntmax
ID: 24188723
There is an option under the properties of the scheduled task to run only if logged on. Make sure this box is not checked.
0
 

Author Comment

by:slamond
ID: 24188821
I added Backup Operator privileges to the Administrator and verified that there is no checkmark in the "Only run if logged in" box. but they report did not run as scheduled a few minutes ago.

I've had similar issues with other tasks recently and it turned out to be an issue with the mapped drive that the task is attempting to write to not being accessible by the Scheduler.
Since Access seems to want to write reports to the My Documents folder (which I normally have repointed to the mapped drive Y: where our Intranet reports are source from) I tried resetting My Documents to the default (C:\Doc&Settings...etc.) but it still writes no reports at the scheduled time.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:tntmax
ID: 24188923
Oh, don't use network drives in your file path. Use the actual UNC (\\10.0.0.1\share\path) instead.
0
 

Author Comment

by:slamond
ID: 24190003
Maybe this is more of a VB code issue.
Here is the modified code with your suggested file path added to only the first report.in this Open Event
(the first query here creates the table for subsequent reports).

After adding this "\\bordensrv\chempaxvb\" path, the HTML report is generated when run manually but still fails when the user is logged out. The scheduled task appears to be working in that it deletes the first page of the HTML file but then stops there.

VB Code starts here:
----------------------------------------------------
Private Sub Form_Open(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryOEDailyOrdersTaken_RECORD2"
DoCmd.Close acQuery, "qryOEDailyOrdersTaken_RECORD2"
DoEvents
DoCmd.OpenReport "rptOEDailyOrdersTaken", acViewPreview
DoCmd.OutputTo acOutputReport, "rptOEDailyOrdersTaken", acFormatHTML, "\\bordensrv\chempaxvb\qryOEDailyOrdersTaken.htm", True
DoCmd.Close acReport, "rptOEDailyOrdersTaken"
DoEvents
DoCmd.OpenReport "rptOEDailyOrdersTakenDETAIL", acViewPreview
DoCmd.OutputTo acOutputReport, "rptOEDailyOrdersTakenDETAIL", acFormatHTML, "qryOEDailyOrdersTakenDETAIL.htm", True
DoCmd.Close acReport, "rptOEDailyOrdersTakenDETAIL"
DoEvents
DoCmd.OpenReport "Bulk Schedule", acViewPreview
DoCmd.OutputTo acOutputReport, "Bulk Schedule", acFormatHTML, "BulkSchedule.htm", True
DoCmd.Close acReport, "Bulk Schedule"
DoCmd.OpenReport "rptOEDailyOrdersTaken_ALL", acViewPreview
DoCmd.OutputTo acOutputReport, "rptOEDailyOrdersTaken_ALL", acFormatHTML, "OEDailyOrdersTaken_ALL.htm", True
DoCmd.Close acReport, "rptOEDailyOrdersTaken_ALL"
DoCmd.OpenReport "rptOEDailyOrdersTakenVEOLIA", acViewPreview
DoCmd.OutputTo acOutputReport, "rptOEDailyOrdersTakenVEOLIA", acFormatHTML, "OEDailyOrdersTakenVEOLIA.htm", True
DoCmd.Close acReport, "rptOEDailyOrdersTakenVEOLIA"
DoCmd.OpenReport "rptOEDailyOrdersTaken_JH", acViewPreview
DoCmd.OutputTo acOutputReport, "rptOEDailyOrdersTaken_JH", acFormatHTML, "OEDailyOrdersTaken_JH.htm", True
DoCmd.Close acReport, "rptOEDailyOrdersTaken_JH"
DoCmd.OpenReport "rptMobileProfitRankJH", acViewPreview
DoCmd.OutputTo acOutputReport, "rptMobileProfitRankJH", acFormatHTML, "rptMobileProfitRankJH.htm", True
DoCmd.Close acReport, "rptMobileProfitRankJH"
DoCmd.OpenReport "rptMobileBulkSchedule", acViewPreview
DoCmd.OutputTo acOutputReport, "rptMobileBulkSchedule", acFormatHTML, "rptMobileBulkSchedule.htm", True
DoCmd.Close acReport, "rptMobileBulkSchedule"
DoCmd.SetWarnings True
End Sub

----------------------------------------------------
end code
0
 

Author Comment

by:slamond
ID: 24215749
I have just realized that my other scheduled tasks that are not using Access and Visual Basic code are running as scheduled.

I'm guessing that my problem is a syntax issue related to the report output (file location) instructions
in the VB code, in the Access form's Open Event.

I need to add this question to the VB Zone, if I can figure out how.
0
 
LVL 15

Expert Comment

by:tntmax
ID: 24215851
Do an admin alert if you want, then put a zone change in the reason.
0
 

Author Comment

by:slamond
ID: 24215948
Thanks tntmax, I did that.
0
 

Author Comment

by:slamond
ID: 24216164
The task now seems to be working.
Not sure why, but I'll leave this question open for a couple of days to be sure.

I had added a user named Robot, gave it Admin and Backup Operator rights and ran the tasks as that user.
I also muddled the potential solution by changing the user's MyDocuments icon so that it points to the \\server\share where the reports are being output to (instead of a mapped drive).

I added to the task security the users Robot and Administrator (in addition to the default "Administrators").

I also added the DoEvents command before the initial query creation commands in the Open Event VB code.

So, if this works, I will need to do reverse engineering to determine the solution.
Which I'll be more than happy to do.
:)
 
0
 

Author Comment

by:slamond
ID: 24217501
For future reference, I'm still curious to learn if zones can be added or modified after the fact?
0
 
LVL 15

Expert Comment

by:tntmax
ID: 24219054
yes, but not by you.
0
 

Author Comment

by:slamond
ID: 24225813
This morning the report process once again is failing, at least partially.
I've noticed that only the first report is being created.

So I made the following changes, mainly adding the explicit \\server\share\ code.
I also removed what I assumed where extraneous "DoEvents" commands.
And so far, all reports are being generated every 10 minutes as scheduled.

----------------------------------------------------------------
Private Sub Form_Open(Cancel As Integer)
DoCmd.SetWarnings False
DoEvents
DoCmd.OpenQuery "qryOEDailyOrdersTaken_RECORD2"
DoCmd.Close acQuery, "qryOEDailyOrdersTaken_RECORD2"
Rem
DoCmd.OpenReport "rptOEDailyOrdersTaken", acViewPreview
DoCmd.OutputTo acOutputReport, "rptOEDailyOrdersTaken", acFormatHTML, "\\bordensrv\chempaxvb\qryOEDailyOrdersTaken.htm", True
DoCmd.Close acReport, "rptOEDailyOrdersTaken"
Rem
DoCmd.OpenReport "rptOEDailyOrdersTakenDETAIL", acViewPreview
DoCmd.OutputTo acOutputReport, "rptOEDailyOrdersTakenDETAIL", acFormatHTML, "\\bordensrv\chempaxvb\qryOEDailyOrdersTakenDETAIL.htm", True
DoCmd.Close acReport, "rptOEDailyOrdersTakenDETAIL"
Rem
DoCmd.OpenReport "Bulk Schedule", acViewPreview
DoCmd.OutputTo acOutputReport, "Bulk Schedule", acFormatHTML, "\\bordensrv\chempaxvb\BulkSchedule.htm", True
DoCmd.Close acReport, "Bulk Schedule"
Rem
DoCmd.OpenReport "rptOEDailyOrdersTaken_ALL", acViewPreview
DoCmd.OutputTo acOutputReport, "rptOEDailyOrdersTaken_ALL", acFormatHTML, "\\bordensrv\chempaxvb\OEDailyOrdersTaken_ALL.htm", True
DoCmd.Close acReport, "rptOEDailyOrdersTaken_ALL"
Rem
DoCmd.OpenReport "rptOEDailyOrdersTakenVEOLIA", acViewPreview
DoCmd.OutputTo acOutputReport, "rptOEDailyOrdersTakenVEOLIA", acFormatHTML, "\\bordensrv\chempaxvb\OEDailyOrdersTakenVEOLIA.htm", True
DoCmd.Close acReport, "rptOEDailyOrdersTakenVEOLIA"
Rem
DoCmd.OpenReport "rptOEDailyOrdersTaken_JH", acViewPreview
DoCmd.OutputTo acOutputReport, "rptOEDailyOrdersTaken_JH", acFormatHTML, "\\bordensrv\chempaxvb\OEDailyOrdersTaken_JH.htm", True
DoCmd.Close acReport, "rptOEDailyOrdersTaken_JH"
Rem
DoCmd.OpenReport "rptMobileProfitRankJH", acViewPreview
DoCmd.OutputTo acOutputReport, "rptMobileProfitRankJH", acFormatHTML, "\\bordensrv\chempaxvb\rptMobileProfitRankJH.htm", True
DoCmd.Close acReport, "rptMobileProfitRankJH"
Rem
DoCmd.OpenReport "rptMobileBulkSchedule", acViewPreview
DoCmd.OutputTo acOutputReport, "rptMobileBulkSchedule", acFormatHTML, "\\bordensrv\chempaxvb\rptMobileBulkSchedule.htm", True
DoCmd.Close acReport, "rptMobileBulkSchedule"
Rem
DoCmd.SetWarnings True
End Sub
-----------------------------------------------------------------------
0
 

Author Comment

by:slamond
ID: 24226602
The task continues to fail today.
It creates the first report only.

When I attempt to run the form manually, I get the following message:



Image1.jpg
0
 

Author Comment

by:slamond
ID: 24227070
Suspecting a conflict with the update query that accesses tblOEDailyOrdersTaken_RECORD, I created a new form that only runs the first query and scheduled that form to run a few minutes before the 2nd form that contains all of the reports.

This will hopefully allow enough time for the initial query to release the lock on that table before the next form attempts to being generating the reports.
0
 

Author Comment

by:slamond
ID: 24242218
OK, I'm officially frustrated.
The task scheduler says that the task last ran 10 minutes ago but the reports are not being updated.
I have to keep the user Robot logged in all day to get the tasks to run.
But I refuse to dedicate a PC to these tasks when they should run without the user logged in.
I have faith in the experts out there......
0
 

Accepted Solution

by:
slamond earned 0 total points
ID: 24374785
I solved this myself by:

1.) Using the Task Scheduler on a local PC to run a "Robot" user session every 30 minutes (to account for any lost connections).

2.) Running the reports within the ERP system (Chempax) to avoid conflicts with other users (this required running another task every 5 minutes to rename the reports database [~RP1.mdb] since it sometimes is renamed [~RP2.mdb or ~RP3.mdb] when Chempax restarts.
The Chempax support team provided the syntax for using a batch file (in the user's Startup folder) to start Chempax with the Robot username and password.

3.) Three different tasks are scheduled in 2 minute intervals. The first one points to the Access form that executes the reports (frmMgtRpt) in c:\Documents & Settings\ robot\Local Settings\Temp\1\~RP1.mdb, the next points to c:\Documents & Settings\robot\Local Settings\Temp\2\~RP1.mdb and the third to c:\Documents & Settings\ robot\Local Settings\Temp\3\~RP1.mdb since the ERP software sometimes renames the TEMP's subfolders as 1, 2 or 3.

4.) Running a scheduled task using TASKKILL iexplore.exe to shutdown Internet Explorer every 10 minutes (to avoid memory overflow from having too many browser windows open).

5.) Running a scheduled task using TASKKILL MSACCESS.EXE to shutdown Access every 10 minutes (to avoid conflicts with the batch file that attempts to rename the reports database to ~RP1.mdb).

This process guarantees (as of 3 consecutive days so far) that the automated reports are generated every 10 minutes (with an occasional conflict in timing that causes a reporting delay of only 20 minutes).


This process could be simplified if anyone knows of a command that will allow renaming of the subfolders from either Temp\2 or Temp\3 to Temp\1.



0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

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 …
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month16 days, 13 hours left to enroll

862 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