Post reply

Using DTS to call execute MS Access

  • June 19, 2007 at 11:08 pm

    #70196

    Hi,

    I'm facing a problem with the below script. It works perfectly fine when I run it from the SQL Enterprise Manager. However when it is scheduled as a job, it fails to call the access file

    Set objShell = CreateObject("WScript.Shell")

    Set objScriptExec = objShell.Exec ("D:\Program Files\Microsoft Office\Office10\MSACCESS.EXE " & """e:\TMS_pre.mdb""")

    Set objShell = Nothing

    In fact, I tried using the process and the same problem occurs.

    win32 process= D:\Program Files\Microsoft Office\Office10\MSACCESS.EXE "

    parameters:"e:\TMS_pre.mdb"

     

     

     

  • June 20, 2007 at 6:20 am

    #714070

    When a DTS package is executed through Enterprise Manager it uses the local resources on the machine when scheduled as a job the package uses the resources on the SQL Server - this will affect things like the file location (D: drive on your machine is not the D: drive on the SQL Server). Are you using EM on the actual SQL Server or from your own machine?

    Also, out of curiosity (and if you don't mind), why are you calling an Access file like this? I take it that it is part of a bigger picture?


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • June 20, 2007 at 7:27 am

    #714097

    I am curious too.  You can insert, delete, update and drop tables from DTS without opening Access. 

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply