Solved

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

Posted on 2013-01-25
5
5,169 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
  • 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I have published numerous articles here at Experts Exchange that present programs/scripts written in a language called AutoHotkey. Each of those articles has a brief paragraph describing where to download the product and how to install it. I have al…
If you get continual lockouts after changing your Active Directory password, there are several possible reasons.  Two of the most common are using other devices to access your email and stored passwords in the credential manager of windows.
This Micro Tutorial will teach you the basics of configuring your computer to improve its speed. It will also teach you how to disable programs that are running in the background simultaneously. This will be demonstrated using Windows 7 operating…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now