Solved

Task Scheduler: MS Access 2010 "Run whether user is logged on or not"

Posted on 2013-01-25
5
5,351 Views
Last Modified: 2013-01-31
I need a solution to run Microsoft Access 2010 macros overnight on Windows 7, using "Run whether user is logged on or not" in Windows Task Scheduler (Start > Control Panel > Administrative Tools > Task Scheduler).  Though I use a domain login, I have administrative rights to my computer.  I have no issue executing batch files for simple tasks: the issue seems specific to Access.

I didn’t have any problem to “Run only when user is logged on”—even if I had my profile locked.  However, my IT department often restarts computers overnight/weekends to push updates, which would render this option unviable.

Windows task scheduler gives the option to “Run whether user is logged on or not,” which would have required only that my workstation be turned on.  However, I have been unable to get Microsoft Access to execute when the user is logged off (and the Task is stuck on “Running”, unless given a timeout, in which case it will think that it executed properly.)


This is the Task that I created to launch Microsoft Access:

Program/Script: C:\Windows\System32\cmd.exe (If I try to execute Access directly from Task Scheduler when logged off, a .laccdb Access lock file is created but will not clear, and the task never executes)
Arguments: /c start "" "C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" C:\TestAccessDB.accdb /X RunMacro


When performing the same task by writing the argument to a batch file (which is what I will do when I have multiple reports that I want to run consecutively), I had the same outcome.

Whenever I selected “Run with highest privileges” and manually executed, I received an error in Access: “Microsoft Access can’t change the working directory to ‘P:\Data’ (mapped network drive). Verify that the drive is valid and the path is 260 characters or less in length.”

Note: There were three server options.  Windows Server 2003 was the only option that worked when I executed the task manually.

With administrator rights, I gave myself “Log on as batch job” rights:
Start > Control Panel > Administrative Tools > Local Security Policy, then Local Policy, User Rights Assignment, Logon as batch job

I granted myself Full Permissions to:
C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE
C:\Windows\System32\Tasks\

I also checked to make sure that the registry was creating the appropriate reference (I will NOT make any changes to my registry):
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Schedule\TaskCache\Tasks

To ensure that this was an Access issue, I ran a test and scheduled the command prompt to execute a batch file, which in turn executed a simple log file.  I logged off of my computer, waited for the scheduled time to pass, and logged back in.  My test worked properly, confirming that the issue is with Access.

Due to company security protocol, I am unable to disable “Users must enter a user name and password to use this computer” in User Accounts. (If I were able to enable this feature, I would write the following to a batch file, then schedule a Task for this batch file to execute at login: rundll32.exe user32.dll,LockWorkStation)

Thanks
0
Comment
Question by:jbanjo
[X]
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
  • 3
5 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 333 total points
ID: 38819271
<<However, I have been unable to get Microsoft Access to execute when the user is logged off (and the Task is stuck on “Running”, unless given a timeout, in which case it will think that it executed properly.)>>

  And you won't.

 When you run a job logged off there is no desktop session, therefore the app cannot interact with the desktop.  For Access, this is a problem.

 The other issue is environment; network drive mapping won't be there, etc and that may cause problems as well.

 Beyond all that, Access has always had quit issues, mainly with hanging references.  It's critical that you close anything you open in code, and set all object variables = nothing.

 Otherwise Access may hang when it goes to quit.

Jim.
0
 
LVL 57
ID: 38819285
I should add I've also had problems with task scheduler itself and older versions of Access.  I've got one client where when run from task scheduler on Windows server 2003, it often generates a C000005 error.

Works fine in every other way, just has a problem quitting.

I worked aound that by configuring Dr Watson to log the fault, but not do anything else with it.

Jim.
0
 

Author Comment

by:jbanjo
ID: 38819508
Thanks Jim.

I have a QuitAccess command at the end of my macro (no saving, and warnings disabled on the macro), and it works well when I'm logged on.  I've read that using VBA to perform this quit is more reliable, but I would still have to call on it via the macro or have it execute when the macro completes.

I had changed any mapped drives to their full paths, but still no resolution.

If there is not a way to run Access unless logged in (or do so effectively and consintently), I think that my best solution is to “Run only when user is logged on” and lock my computer each night before I leave.  If a restart occurs, I can have the task launch at my next login.  I will track report execution times in Access and, on the event that a particular night is skipped, a macro can have the report run for each night since the last successful execution.

Please let me know if you think of a better alternative.

Thanks!
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 333 total points
ID: 38819687
<<If there is not a way to run Access unless logged in (or do so effectively and consintently), I think that my best solution is to “Run only when user is logged on” and lock my computer each night before I leave. >>

 That's what I do.

<<Please let me know if you think of a better alternative.>>

 None that I'm aware of, but let's see if anyone else jumps in with a comment...

Jim.
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 167 total points
ID: 38839042
Just seconding what Jim said, and giving you a couple of examples of how we have worked with this -

One is a large parts/purchasing/work request system.  We have preventive maintenance and routine tasking components in this system - both of which require daily emails to be sent out.  The start-up form in this database has a user-group check.  If the user logging in is in the 'Technical Data Management' group (3 people, at least one of whom is just about guaranteed to open the database on any given day) it triggers the routine emails, and checks a box when done so that the next login does not trigger the emails.  So the first Technical Data Management user to log in on any given day will automatically send out the emails.

Another database system needs to run scheduled reports.  We have a fake user for this (ReportsUser) which is always logged in on a dedicated computer, which we just leave turned on, locked with the database always open and this user logged in.  The reports are run at timed intervals.
0

Featured Post

RoboForm Secure Password Management System

RoboForm Everywhere - Superb Browser Support
Windows / Apple / IOS / Android / Linux / Chrome OS
Use different complex passwords everywhere
Best Secure Password Management by far
Synchronize all of your devices instantly
Safe, Secure & Highly Recommended!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The Task Scheduler is a powerful tool that is built into Windows. It allows you to schedule tasks (actions) on a recurring basis, such as hourly, daily, weekly, monthly, at log on, at startup, on idle, etc. This video Micro Tutorial is a brief intro…

734 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