How to create a job to execute an Access program?

I am trying to schedule an Access Job using SQL Server Agent. I have
created a .BAT file with the full path to a Access .mdb file. The Access
program will automatically start up a form and copy data from an Orcale
database into the SQL Server. When I execute the .BAT file by hand (by
double-clicking it) everything works fine. I can't get things to work
when I create a job and specify the full path to the .BAT file in the
command window. The following is what I enter in the command window: "G:
\SQLJobs\GetData.mdb". I get no errors in the event log. Can someone
give me an idea on what I need to do. I assume I am missing something.
Thanks.
John
Hi
Scheduling an interactive program is not a good idea. You may want to change
this to use a linked server and have it totally within SQL Server itself or
you may want to look at DTS as an another alternative.
John
"John" <nomail@.none.com> wrote in message
news:MPG.1ccc2ae08c2915d9989682@.news.comcast.gigan ews.com...
>I am trying to schedule an Access Job using SQL Server Agent. I have
> created a .BAT file with the full path to a Access .mdb file. The Access
> program will automatically start up a form and copy data from an Orcale
> database into the SQL Server. When I execute the .BAT file by hand (by
> double-clicking it) everything works fine. I can't get things to work
> when I create a job and specify the full path to the .BAT file in the
> command window. The following is what I enter in the command window: "G:
> \SQLJobs\GetData.mdb". I get no errors in the event log. Can someone
> give me an idea on what I need to do. I assume I am missing something.
> Thanks.
> John
|||I think you should point the path to "access.exe", something like this:
"C:\program files\microsoft office\office\access.exe g:\...\GetData.mdb"
But I really do not know if it good idea to run such external process by
using SQL Server Agaent. SQL Server agent (and SQL Server) has no control to
the Access app except for starting it. That is why you do not have error
logged. To schedule this Access app, I'd simply use Windows' "Scheduled
Tasks" applet in Control Panel.
Specific to your task, using DTS might be more suitable.
"John" <nomail@.none.com> wrote in message
news:MPG.1ccc2ae08c2915d9989682@.news.comcast.gigan ews.com...
> I am trying to schedule an Access Job using SQL Server Agent. I have
> created a .BAT file with the full path to a Access .mdb file. The Access
> program will automatically start up a form and copy data from an Orcale
> database into the SQL Server. When I execute the .BAT file by hand (by
> double-clicking it) everything works fine. I can't get things to work
> when I create a job and specify the full path to the .BAT file in the
> command window. The following is what I enter in the command window: "G:
> \SQLJobs\GetData.mdb". I get no errors in the event log. Can someone
> give me an idea on what I need to do. I assume I am missing something.
> Thanks.
> John