Task Scheduler - MS Access.
Hi,
I am trying to run the MS Access file via task scheduler. Task Scheduler run on time and MS Access start in Task manager after one minute Excel also started but than nothing. Both process stay at task manager and nothing happen. I waited for hours. Please review the 1st image.
I tried everything adding path into trusted location. Please review 2nd image but same result. I need to End task both applications otherwise it will stay in task manager.
This is my code: If i run this code via command prompt than everything is working fine. If i create a task scheduler as per below code than MS Access/Excel start but stay in task manager.
"C:\Program Files\Microsoft Office\Microsoft Access 2010\MSACCESS.EXE" "C:\DbPath\DbName.mdb" /xMacroName
I create batch file of above code and run via task scheduler but same thing happened as I explain above.
Note: We are migrating the applications from Server 2012R2 to Server2016. Same code/procedure is working fine in windows server 2012R2. Means Task scheduler is working fine in 2012R2 but its not working with 2016. Both servers are work group and login user have full administrator rights.
Just let you know that I need to assign the Batch Job Rights to user via this link 'local group policy' " Batch Job Rights otherwise user receive the error. Please review 3rd image.
Please help me to resolve this issue.
I am trying to run the MS Access file via task scheduler. Task Scheduler run on time and MS Access start in Task manager after one minute Excel also started but than nothing. Both process stay at task manager and nothing happen. I waited for hours. Please review the 1st image.
I tried everything adding path into trusted location. Please review 2nd image but same result. I need to End task both applications otherwise it will stay in task manager.
This is my code: If i run this code via command prompt than everything is working fine. If i create a task scheduler as per below code than MS Access/Excel start but stay in task manager.
"C:\Program Files\Microsoft Office\Microsoft Access 2010\MSACCESS.EXE" "C:\DbPath\DbName.mdb" /xMacroName
I create batch file of above code and run via task scheduler but same thing happened as I explain above.
Note: We are migrating the applications from Server 2012R2 to Server2016. Same code/procedure is working fine in windows server 2012R2. Means Task scheduler is working fine in 2012R2 but its not working with 2016. Both servers are work group and login user have full administrator rights.
Just let you know that I need to assign the Batch Job Rights to user via this link 'local group policy' " Batch Job Rights otherwise user receive the error. Please review 3rd image.
Please help me to resolve this issue.
ASKER
Hi,
Thanks for the reply. I installed the SQL Server 2017 Express with same user that run the task scheduler.
How I can check the permission? In security tab user have full control.
Is network access is really necessary because its a single server and everything on same server.
Thanks for the reply. I installed the SQL Server 2017 Express with same user that run the task scheduler.
How I can check the permission? In security tab user have full control.
Is network access is really necessary because its a single server and everything on same server.
Check the user permissions of the database you like to access in SSMS.
ASKER
databases are restore via sa user not current local (full admin) user.
we are migrating server from 2012R2 to 2016.
would you like me to add this local user in databases logins and assign full permission and than check it.
we are migrating server from 2012R2 to 2016.
would you like me to add this local user in databases logins and assign full permission and than check it.
ASKER
I added my local user in SQL security login.
Assign full permission on all databases. Run the task scheduler but same result. Both process stay in task manager and doing nothing with 0 resources.
Assign full permission on all databases. Run the task scheduler but same result. Both process stay in task manager and doing nothing with 0 resources.
If it was running fine in 2012 and now it hangs then probably you just missed something.
Start simple...create a bat file that does something extremely simple like print something or whatever...execute it normally...does it does what it supposed to do ?
Now task scheduler...check...does it run OK ?
Supposing you managed to make it work...now its time to put the Access in the .bat...execute it normally... does it opens the Access...now again scheduler...again ...the same..
Also there are some mentions that "Run whether user is logged on" might interfere..so uncheck it if checked or the opposite.
Start simple...create a bat file that does something extremely simple like print something or whatever...execute it normally...does it does what it supposed to do ?
Now task scheduler...check...does it run OK ?
Supposing you managed to make it work...now its time to put the Access in the .bat...execute it normally... does it opens the Access...now again scheduler...again ...the same..
Also there are some mentions that "Run whether user is logged on" might interfere..so uncheck it if checked or the opposite.
1. Are you trying to run this on a Server or on an ordinary PC on the network.
2. If on the Server, are you sure that Office is installed? Most companies won't load Office on their servers.
3. Once you get past 1 & 2, you might try creating a .bat file to house the command to open the application and run the .bat file from the Scheduler rather than referencing Access directly.
2. If on the Server, are you sure that Office is installed? Most companies won't load Office on their servers.
3. Once you get past 1 & 2, you might try creating a .bat file to house the command to open the application and run the .bat file from the Scheduler rather than referencing Access directly.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
Yes, if i set the task to run "only when the user is logged on" than it was work fine but why its not working with option "Run whether user is logged on or not"?
Its really confusing, same setting working in server 2012R2 but not working in server 2016.
Yes, if i set the task to run "only when the user is logged on" than it was work fine but why its not working with option "Run whether user is logged on or not"?
Its really confusing, same setting working in server 2012R2 but not working in server 2016.
Maybe this question on spiceworks will help : https://community.spiceworks.com/topic/1989679-task-scheduler-and-run-whether-user-is-logged-on-or-not
Its either permissions or bad mapping
Its either permissions or bad mapping
<<Yes, if i set the task to run "only when the user is logged on" than it was work fine but why its not working with option "Run whether user is logged on or not"?>>
McKnife hit it on the head and the reason is Office (and Access) are considered a "foreground app".
What that means is that they expect a desktop environment to run in and what you'll find is that it is almost impossible to run an Office app in a logged out state. There are rare instances when you can, but that is not the norm. It expects things like mapped drives, printers, etc to be available and all kinds of different things will happen when not.
You can leave the console locked, but a user must be logged in and that user is the one that the Access task must be using.
Jim.
McKnife hit it on the head and the reason is Office (and Access) are considered a "foreground app".
What that means is that they expect a desktop environment to run in and what you'll find is that it is almost impossible to run an Office app in a logged out state. There are rare instances when you can, but that is not the norm. It expects things like mapped drives, printers, etc to be available and all kinds of different things will happen when not.
You can leave the console locked, but a user must be logged in and that user is the one that the Access task must be using.
Jim.
My experience:
* The only case I had running Access with UI interaction were some GDI related things. Everything else works perfectly under a normal account assigned the necessary permissions. Drives, printers etc. are only a setup issue.
* Excel and PowerPoint require sometimes that the document is visible.
* And I never got Outlook automation running without interactive logon.
And I forgot to mention that earlier:
You need complete error handling in every procedure to avoid error pop-ups. And of course, your normal code executed must be without popups also.
* The only case I had running Access with UI interaction were some GDI related things. Everything else works perfectly under a normal account assigned the necessary permissions. Drives, printers etc. are only a setup issue.
* Excel and PowerPoint require sometimes that the document is visible.
* And I never got Outlook automation running without interactive logon.
And I forgot to mention that earlier:
You need complete error handling in every procedure to avoid error pop-ups. And of course, your normal code executed must be without popups also.
ASKER
Thanks guys for all the suggestion and recommendation. I run the task with ‘Run only when user is logged on’ option. All good.
Another approach:
Start Access using a Timer. When the appropriate time to start occurs the processing kicks off.
So instead of using Task Scheduler I use Access itself as a Scheduler.
After the processing is complete Access shuts down.
Start Access using a Timer. When the appropriate time to start occurs the processing kicks off.
So instead of using Task Scheduler I use Access itself as a Scheduler.
After the processing is complete Access shuts down.
Just to add to that a bit; it's more flexible if you just use the timer to "wake up" and check for any pending tasks. I usually set it for a minute, then have a tasks table to carry out the various items.
Only problem with this approach though is that you don't get a clean slate memory wise as Access is always running, which is why I moved away from this quite a while back.
For automation stuff, I use a lot of small focused DB's that fire off with my own Access based task scheduler rather than one monolithic app that does everything.
Still though, it all boils down to have a logged in user :(
FWIW,
Jim.
Only problem with this approach though is that you don't get a clean slate memory wise as Access is always running, which is why I moved away from this quite a while back.
For automation stuff, I use a lot of small focused DB's that fire off with my own Access based task scheduler rather than one monolithic app that does everything.
Still though, it all boils down to have a logged in user :(
FWIW,
Jim.
I have a number of batch processes. They run on a client computer and use Windows Scheduler. They ALL execute a .bat file which runs a macro in the database. Access shuts down when the task is finished. The tasks are set to run whether the user is logged in or not and in fact, we leave the PC on but not logged in. In my case, the key was to use the batch file rather than running Access directly.
So I would test your scheduled task by running it under your user account.