Calling DTS from Access - URGENT PLEASE HELP

[Madhuri]

Hi all

I have a msaccess form from which I want to call SQL server DTS.

1) I have created a stored procedure calling the dts.
2) The rundts() function is calling the stored procedure

The problems I am facing is
1) The dts is not getting executed as its coming out in no time and no output.

Is the RUNDTS() simply executing make procedure instead of executing dts ?

This is the code for stored procedure
--Start procedure
CREATE PROCEDURE sp_ServiceRequesttoAccess @error bit OUTPUT
AS
DECLARE @shell varchar(255)
SET @shell = 'dtsrun /SOADFILE /N"sp_servicerequesttoaccess" /U"kpuser"
/P"Kaiser"'
exec @error = master..xp_cmdshell @shell
return @error
GO
--end procedure

--Function RunDTS()
Private Function RunDTS() As Boolean

On Error GoTo error_

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

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)
Set rsTemp = objSproc.Execute()
If .Parameters("@error") = True Then
MsgBox "Error in DTS"
Else
MsgBox "Successful"
End If

MsgBox .Parameters("@ERROR")

'.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

Any help in this regard would be appreciated
Thanks
Madhuri

 

[Terry Kreft]

Well the first thing I notice is that the SPROC doesn't return a recordset
so instead of

With objSproc
.CommandType = adCmdStoredProc
.CommandText = "sp_ServiceRequesttoAccess"
.ActiveConnection = conn
.Parameters.Append .CreateParameter("@Error", adBoolean,
adParamOutput, 1, False)
Set rsTemp = objSproc.Execute()

You can have
With objSproc
.CommandType = adCmdStoredProc
.CommandText = "sp_ServiceRequesttoAccess"
.ActiveConnection = conn
.Parameters.Append .CreateParameter("@Error", adBoolean,
adParamOutput, 1, False)
.Execute

Also if conn is a connection object you should Set the .Activeconnection
property
With objSproc
.CommandType = adCmdStoredProc
.CommandText = "sp_ServiceRequesttoAccess"
Set .ActiveConnection = conn
.Parameters.Append .CreateParameter("@Error", adBoolean,
adParamOutput, 1, False)
.Execute

and instead of creating the return param you could just refresh the
..Parameters collection
With objSproc
.CommandType = adCmdStoredProc
.CommandText = "sp_ServiceRequesttoAccess"
Set .ActiveConnection = conn
.Parameters.Refresh
.Execute

Then after all that you need to look at the security context in which your
DTS package is being run.