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

Posted on 2013-01-25
Medium Priority
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

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)

Question by:jbanjo
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
LVL 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 1332 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.

LVL 58
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.


Author Comment

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.

LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 1332 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...

LVL 61

Assisted Solution

mbizup earned 668 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.

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This is a fine trick which I've found useful many times, when you just don't want to accidentally run a batch script or the commands needs administrator rights.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This Micro Tutorial will go in depth within Systems and Security in Windows 7 and will go into detail regarding Action Center, Windows Firewall, System, etc. This will be demonstrated using Windows 7 operating system.
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…
Suggested Courses
Course of the Month10 days, 20 hours left to enroll

770 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