Solved

Scheduling Access

Posted on 2011-03-21
13
327 Views
Last Modified: 2012-05-11
Hi,

I have a scheduled task to open an Access DB which then runs a macro.

If i do this manually, it is fine, if i schedule it over night it does not run. Is there an issue with the fact im logging off?

Thanks
Seamus
0
Comment
Question by:Seamus2626
  • 6
  • 5
  • 2
13 Comments
 
LVL 6

Expert Comment

by:Greenclock
ID: 35179256
You need to schedule the job using a User that has Administration rights. Using the system account does not work

Take a look at the following Microsoft Document

http://support.microsoft.com/kb/230575

Hope this helps

Greenclock
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35179274
FWIW, if you use this, log on/log off is irrelevant:
http://www.fmsinc.com/microsoftaccess/Scheduler.html

0
 

Author Comment

by:Seamus2626
ID: 35179922
Thanks guys, ive went through the steps from the Microsoft site but i cant seem to get it going.

Ive typed the below into Notepad and saved it. It wont open though, and the error message appears very quickly and goes away

C:\Program Files\Microsoft Office\officeMsaccess.exe C:\Copy of Triple Link_2011-03-15.mdb /x Macro1
 /x Macro1

Is there an error in the above path possibly?

Thanks
Seamus

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Seamus2626
ID: 35180371
I tried putting it in the CMD editor and get the following error message

Z:\>c:\program files\microsoft office\office Msaccess.exe\c:northwind.mdb/x macr
o1
'c:\program' is not recognized as an internal or external command,
operable program or batch file.

Thanks
Seamus
0
 
LVL 6

Expert Comment

by:Greenclock
ID: 35180426

Seamus

Try putting Quotes round items in the command line.  The problem is that there are spaces in folder/file names and these are causing it to be treated as 2 or more command line items.

C:\Program Files\Microsoft Office\officeMsaccess.exe "C:\Copy of Triple Link_2011-03-15.mdb" /x Macro1

Greenclock
0
 
LVL 6

Expert Comment

by:Greenclock
ID: 35180446

For the command prompt test........

"c:\program files\microsoft office\office Msaccess.exe" "c:{full path}\northwind.mdb" /x macro1

Greenclock
0
 

Author Comment

by:Seamus2626
ID: 35182110
Thanks Greenclock.

I saved a file called MyTes.bat and entered

cmd.exe/c
"c:\northwind.mdb" /x macro1

That works fine if i double click it, but wont run when im logged off, it says in the scheduled taks "running"

Its using my user name where its says "Run as"

Do you think its an administration issue?

Thanks
Seamus


0
 
LVL 6

Expert Comment

by:Greenclock
ID: 35182947
Seamus


Which Operating System and version of MS Access are you using?

Greenclock
0
 

Author Comment

by:Seamus2626
ID: 35183000
Its 2003 Access and Microsoft Windows XP Professional

Thanks
Seamus
0
 
LVL 6

Expert Comment

by:Greenclock
ID: 35183223
OK

- Does the macro have an entry to exit MS Accees?  
This may explain why it keeps on running...

- Is the ID you setup the job with and Administrator?
 Reason: To run a task while not logged on you need "Run As a Batch" rights.  The administrator access will allow this when the job is created.

You can grant this right using the "Local Policy Settings" Tool.

Type the following at the command line:
     ntrights.exe -u {user or Groupname} +r SeBatchLogonRight

If you get an error then the Windows Resource kit is is not install
http://go.microsoft.com/fwlink/?LinkId=4544

Greenclock
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 150 total points
ID: 35183714
To run a scheduled task you must have Admin rights.

In other words "you must be a member of the Administrators group" in order to run a scheduled task.
So you will have to set this up in the "Run As" option when setting up your scheduled task.

See Here:
http://technet.microsoft.com/en-us/library/cc786711%28WS.10%29.aspx

And here:
http://technet.microsoft.com/en-us/library/cc780931%28WS.10%29.aspx

See the attached screenshot as well, it shows where this is set up when you create a a Windows Scheduled task.

Does this all help?

JeffCoachman
untitled.JPG
0
 
LVL 6

Accepted Solution

by:
Greenclock earned 350 total points
ID: 35183866

In this case getting the MS Access job running successfully I would recommend using an ID with Administrator righs as mentioned by Jeff. But only in the first instance to get this working, but this is not something that is required permentally.  A lot of damage can be cause if the script goes wrong.

Administrators have the Batch Right by default, but it is possible to run a task, while not logged on, with a non-Administrators ID provided the "Log as a Batch" right has been.


Greenclock
0
 

Author Closing Comment

by:Seamus2626
ID: 35188238
Thanks guys, need to get myself set up with Admin rights

Cheers,
Seamus
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up 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.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

830 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