Running DTS Package from MS-Access (mdb)

[azvlado]

Hi,

Does anyone know if it is possible to run a DTS Package from MS-Access database (.mdb)? If yes, could you please specify how to do that? Thanks!

Regards,
av

[rmiao]

Yes if you can call dtsrun in access.

[Gorata]

Hi Azvlado

Try this code

===== Access VBA =======

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"


With objSproc
.CommandType = adCmdStoredProc
.CommandText = "Your stored procedure"
.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


===== Stored Procedure ========

CREATE PROCEDURE sp_YourProcedure @error bit OUTPUT
AS
DECLARE @shell varchar(255)
SET @shell = 'dtsrun /S"ServerName" /N"DTSName" /U"sa" /P"Password"'
exec @error = master..xp_cmdshell @shell
return @error
GO


==========================
In stored procedure

• No quotes around ServerName etc
• Username must have Admin right hence sa

Hope this sorts you out