Access 2010 Macro on Server 2008 R2 Task Scheduler Unattended

Question

I've seen many questions like this but none of the suggested solutions are working for me.

I have an Access 2010 Macro named "MAIN" (on 32-bit Office). The database contains ODBC linked tables against different database servers and complex join queries to compare and analyze the data to produce CSV files for importing into a third-party system. The Macro is designed to automatically run these queries and save the results to CSV files for later use by the third-party import tool.

My desire is to run this macro "unattended" so that the files are created on a set schedule regardless of whether a user is logged into the system or not. The target system is running Server 2008 R2 (natively 64-bit Windows).

Many of the answers I see online indicate that Office Automation macros may require a user to be logged in so that the Office program has a valid user desktop session. There are several people who are giving the opposite answer - saying that they can run a Microsoft Access Macro unattended using the Windows Task Scheduler with the option "Run whether user is logged on or not" selected.

The Action is set to "Start a program", with the Program/script set to "C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" and arguments set to "C:\Test_Dir\Test_DB.accdb" /nostartup /x MAIN

When I run the scheduled task either at the set time or on-demand, it completes quickly with the MSACCESS ResultCode (return value) of 2004 (equivalent to 0x7D4 hex Run Result). Viewing the output folder shows that no files were created.

If, however, I set the Task "Security options" to "Run only when user is logged on", it runs successfully with a proper ResultCode of 0 (0x0 hex). The output files are created as expected in the desired folder, but this only works while I'm logged into the system.

Please provide potential solutions if you have any suggestions that might work. It seems like I've tried so many alternatives but I must be missing something because nothing is working for scheduling an unattended Access macro while logged out.

Answer 1

I think your misunderstand that "run only" when user is logged on. This does NOT mean you going to run Access without any "user" but ONLY means that if a human being is using the computer at that time, THEN run the task.

The key issue then is WHAT USER is logged on WHEN the task is supposed to run?

In other words there will ALWAYS IN EVERY case be SOME user that is logged on to run this task. (the option to "run only" when someone is logged on means that if you logoff and go away on holidays then the task will not run.

However if you choose run if user is not logged on? Well then the COMPUTER STILL will have a user logged on when you run the task! The problem is this is the default account for that computer – often different than your current logon you are using for testing.

So in the task manager, you free to choose when the user is not logged on, but you better THEN be sure that the default machine account used by the task manager has permissions to that database or better yet just specify the logon in the task scheduler.

I would MUCH suggest that you use the task scheduler to SPECIFY a user to run the task – and specify the current same user that you know works.

Answer 2

It turns out that my problem was related to MSACCESS.exe being stuck under the task scheduler engine (taskeng.exe) with some failed test run and must have had a dialog box that no screen was connected to.

Even though I had used the Windows Task Scheduler to "End" the task, the MSACCESS.exe and taskeng.exe processes were still out there in the background causing my following Task tests to fail unless I ran them only as logged in (then they run as standard process rather than under the task host/task engine).

I had to use the Task Manager (taskmgr.exe) to show processes from all users (including scheduled tasks), sort by image name, look for any instances of "MSACCESS.exe" or "taskeng.exe" and End Process for each.

I'm sure a simple reboot would have cleared out these stuck processes too, but this is a shared Remote Desktop (Terminal Services) Server and I can't reboot it while others are logged in.

After clearing those stuck processes out, the task ran unattended for me as needed both with Integrated Windows Authentication (Trusted_Connection), and with standard SQL Authentication username/password database connectivity for ODBC linked tables.

I did also test using the built-in "SYSTEM" user (NT Authority\System) in hopes that the task could run and never require a user password change, but the task failed and I had to use taskmgr.exe to clear out the stuck processes again even after ending the scheduled task. This was probably how I caused the problem to begin with. The SYSTEM account must be having a problem running MSAccess.exe - maybe a dialog box is popping up that I can't see.

For now it's working, I'll run the scheduled task under a standard domain user account and update the password in the task properties whenever the user account has a password change.

Answer 3

I use 4 Access Database 2010 on Server 2008 R2 with Task sheduler in same manner as written above, also with the same adjustments. 3 of 4 tasks run with result 0x0 errorfree, one of the tasks ends also with error 2004. It seems that the problem is located in the database. Due to the fact, that all database are a copy from the same default database, the only different values are at least the much larger count of recordsets. The solution could be to implement some waitstaits during the exporting scripts before starting the application.quit command.

Answer 4

The key phrase is Remote Terminal (or Citrix). It is not wise to have all users start the same copy of the MS Access Project. (.ACCDB or other variations) Use a script to take the master copy (or current version) and copy that into a user folder. Then launch the copy in the user folder.

Also, instead of the ODBC for linked tables. Have the Access programmers use the DSN-Less code to establish the connections. For SQL Server - install the SQL Server Native Client 11.0 on the Remote Terminal (or Citrix) server. A vba script can create the connections to SQL Server. This has the advantage of working with AZURE.

By using the split database, the Front-End running a copy for each user connected to the back end should be scaleable to over 200 concurrent users assuming no lengthy record locking is used in the form's code.

Answer 5

In my experience, checking "run whether user is logged on or not" was actually the cause of those phantom MSACCESS processes. When it ran, it would try to run Access as some default user in the background, and then crash, leaving the process running. Apparently MSOffice does not like to run non-interactively.

I also (for reasons I have yet to determine) had trouble with checking the "run with highest privileges" box. I was trying to move a task to a new PC. It ran fine on the old PC, but on the new one I would get an error when trying to create an Outlook object. I compared the two tasks and kept changing options until I narrowed it down to the "run with highest privileges box". Checked: no go; unchecked: ran fine.