Server job to run Access macro (1 Viewer)

[bobfin]

I'm trying to create a nightly job that will run a macro in an Access database. The database is called Stats.mdb and the macro is called mcrDaily. The single job step has a type of "Operating System Command" and has the following code:

"C:\Program Files\Microsoft Office\Office11\MsAccess.exe" \\garfield\Stats\Stats.mdb /x mcrDaily

I first tested a version of this job that only opened Access (just the code between the quotes). Nothing happened. Have I got the syntax wrong or is there another way to run Access from SQL code?

 

[pdx_man]

Well, something is always going to happen. Did you refresh the job listings? Your job should have had it's icon change to a red circle with an X in it. Right mouse click on it to view the job history and put a check in the Show Step Details box. This should tell you what is wrong.

Also know that the path is relative to the server. If you are referencing the C:\ drive in your path, the job is going to think you are talking about the C:\ drive on the server.

Question for you ... what not just create a shortcut to the macro and then use Windows Schedular? Another ... can you leave the DB open?

Right mouse click on macro, select create shortcut. Then go to Control Panel, Scheduled Tasks and schedule a task ...

That is what I would do if I couldn't just leave the DB open. That would be my preferred choice. Have a hidden form in the background that continuously checked what time it was and ran the macro at the assigned time.

 

[bobfin]

All the tests used code to just start Access (no mention of a database or macro). I keep getting a "file not found" error even tho it is the correct path on the server drive. I removed the double quotes & got the same error. I also tried the following code (same results):

Exec master..xp_cmdshell "C:\Program Files\Microfsoft Office\Office11\msaccess.exe"

We had tried leaving a PC running all night with a Scheduled Task, but periodically got "can't find the dabase" errors. It's preferable to have SQL Server 2000 do this because then all the nightly jobs are managed from one place and because there are many other Access tasks that need to run at night. Because those tasks involve dozens of queries & macros, we can't convert them to ADPs any time soon.

So can an Access macro be run from a server job?

 

[pdx_man]

Well, I don't see why not, if you create a shortcut to the macro out in a location that the server can get to and this location also needs to have the proper rights as the account that SQL Agent is running under.

This may be your issue right there. Under what account is SQL Agent running? Is it a domain admin? You may want to play with that.