?
Solved

Scheduled Task will not work properly to run Access Macro

Posted on 2008-09-29
18
Medium Priority
?
650 Views
Last Modified: 2013-11-27
I have a macro in Access that I need to run once each day.  I created the macro, then created a scheduled task to execute the macro.  When I right click and select "run" on the scheduled task it works perfectly.  

However, once logged off and I let it run on its regular schedule it is not working.  Access is indeed opening, but it is not running propely.  It starts to run and opens Access, but it would appear the macro never runs and Access is remaining open, as if it is getting hung up.  I have to go in some time later and kill the MSAccess process.  However, if I am logged on I can watch the scheduled task execute and work properly.  

I have checked permissions on all folders as well as the database that contains the macro and the adminstrator has full control.  The scheduled task executes as the administrator as well.

Any advice would be appreciated!
Thanks!
0
Comment
Question by:dcgimo
  • 9
  • 7
  • 2
18 Comments
 
LVL 18

Expert Comment

by:jmoss111
ID: 22600789
In the scheduled task, is the only run if logged in checkbox checked?
0
 

Author Comment

by:dcgimo
ID: 22600825
No, it is unchecked.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22600847
Is your password set?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:dcgimo
ID: 22600862
Yes, and I am not getting any errors or anything that would make me think it is incorrect.  I have even tried to recreate it several times just in case, and also had someone else type it.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22600866
Or should I say has your login password changed since you set the task password?
0
 

Author Comment

by:dcgimo
ID: 22600879
Nope, no changes to it.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 22600887
I believe the problem is that Access will only run when the box is logged in even if the screen is locked. It may not work on a locked screen either.

Access is not a service and depends on the ability to output to the screen in some fomr or fashion. I have never really tried to run Access without the server/workstation logged in, but I don't think it will work.
0
 

Author Comment

by:dcgimo
ID: 22600903
Hmmm, maybe you are right.  Perhaps I can run it in a sql query instead if needed.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22601080
Do you log in through a domain?
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22601126
I log in through a domain and have to change the password in the task scheduler for each task every 30 days when my password expires or my scheduled jobs wont run.

Jim
0
 

Author Comment

by:dcgimo
ID: 22604255
Jim- I log in as the adminstrator on the server and that password does not expire.  I saved the password in the task again just in case and still had the same results.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 22604654
I don't think you can run Access unless the server is logged in. Only services can be run, and Access is looking for "user interaction" even with the macro.
0
 
LVL 18

Accepted Solution

by:
jmoss111 earned 2000 total points
ID: 22609625
I have jobs that run early every morning, some 5 days a week, some 7 days and I'm not logged in.

The Access dbs do load a form at startup and my code is in the ONLoad event.

Jim
SchedScreenShot.doc
0
 

Author Comment

by:dcgimo
ID: 22617265
Jim- it looks like you are simply directing the scheduled task to open Access, but Access itself runs your tasks as soon as it opens.  Do you have problems with Access closing after the fact?  How are you handling that?  I'm going to give this a try!
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22619720
Nope, it does it's process, send mail notification and closes. I never touch it. I have multiple jobs like this performing different tasks running on multiple systems and I never have to touch them.

I do put a DoCmd.Quit at the end of the process, thats all.

Hope it works out for you. Let me know.

Jim
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22619746
I have a RAOC (random array of obsolete computers) in a cubicle that perform loads, reporting, mailing, ftp, data hygiene, etc.
0
 

Author Comment

by:dcgimo
ID: 22670314
Jim-  this was a great help.  I think the key point that you mentioned was that you are placing your code on a form.  I had everything in a macro and was running the macro and then it was getting stuck (running queries via the macro).  I broke everything down and put it in the onopen event on a form.  I then directed my scheduled task to run a macro that simply opens that form.  Everything is working great now and Access is closing properly with the docmd.quit command.  Thanks for all your help!
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22670577
You're welcome dcqimo; and thanks for the nice comments!

You might want to shift that to the OnLoad event...

I'm glad that I was able to help.

Jim
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
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…

621 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