Solved

Scheduled Task will not work properly to run Access Macro

Posted on 2008-09-29
18
635 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 500 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Dcount unique 6 24
Syntax error with between dates 6 18
Trying to open a report with a filter 2 17
DCount using "OR" 4 19
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
On July 14th 2015, Windows Server 2003 will become End of Support, leaving hundreds of thousands of servers around the world that still run this 12 year old operating system vulnerable and potentially out of compliance in many organisations around t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now