Hi all

I am trying to execute a sql server DTS package in MSAccess and the code in
the form goes like this

Private Function RunDTS() As Boolean

On Error GoTo error_

Dim conn As ADODB.Connection
Dim objSproc As ADODB.Command

Set conn = CreateObject("ADODB.Connection")
Set objSproc = CreateObject("ADODB.Command")

'conn.Open "Connection String"

Set conn = AdGcn

With objSproc
   .CommandType = adCmdStoredProc
   .CommandText = "sp_ServiceRequesttoAccess"
   .ActiveConnection = conn
   .Parameters.Append .CreateParameter("@Error", adBoolean, adParamOutput,
1, False)
   .Execute
   If .Parameters("@ERROR") = True Then 'Error in DTS...
       Err.Raise "9967", , "Failed to run DTS package" & vbCrLf & "Call IT
development for assistance"
   Else
       MsgBox "Data transfer Successful"
   End If
End With

Set objSproc = Nothing
Set conn = Nothing
RunDTS = True

exit_:

Exit Function

error_:

Select Case Err.Number
Case Else

   MsgBox Err.Number & " " & Err.Description

 
   RunDTS = False
   Resume exit_
End Select

End Function

The stored procedure goes like this
CREATE PROCEDURE sp_ServiceRequesttoAccess @error bit OUTPUT
AS
DECLARE @shell varchar(255)
SET @shell = 'dtsrun /S PCGOADFILE /N "Kaiser_OCI_ServiceRequesttoAccess"/U
"sa" /P "Password"'
exec @error = master..xp_cmdshell @shell
return @error
GO

I am not able to run the DTS... giving me an error... Please help

Thanks
Madhuri