Error Executing DTS package from MSACCESS

[Madhuri]

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

 

[Chaim]

Not sure if this just a typo in the post or the code, but you need a space
between ".....Access" and '/U' in the "SET @shell" line.