Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Scheduled Task will not work properly to run Access Macro

Posted on 2008-09-29
18
Medium Priority
?
648 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

916 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