?
Solved

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

Posted on 2013-01-25
5
Medium Priority
?
5,535 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 58

Accepted Solution

by:
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.

Jim.
0
 
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.

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

Jim.
0
 
LVL 61

Assisted Solution

by:mbizup
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.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will give you a basic overview of Windows DVD Burner through its features and interface. This will be demonstrated using Windows 7 operating system.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 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