Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Scheduled Task will not work properly to run Access Macro

Posted on 2008-09-29
18
Medium Priority
?
646 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

Technology Partners: 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!

Question has a verified solution.

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

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.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

722 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