Using a DTS package via MS Access

[OhioSteve]

I am attempting to execute a DTS package from an MS Access DB.

1) I consulted this resource:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q315661&

2) I added a reference to the "Microsoft DTSPackage Object Library" in my Access DB.

3) I added this code to the MS Access button:
Dim dtsp As New DTS.Package
dtsp.LoadFromSQLServer _
ServerName:="serverName", _
ServerUserName:="userName", _
ServerPassword:="userPassword", _
PackageName:="packageName"
dtsp.Execute

4) I am getting the error "DTS package does not exist". But I know that it DOES exist.

I would appreciate whatever suggestions you have for me.

 

[smedvid]

Here is my approach..
I create a Job that calls the DTS via a DTSRun Command. The advantage is getting an email when the DTS is completed. I then constructed a Stored Procedure in SQL Server to automatically create the Job on the fly and then delete it once it is finished. You can then call the Stored Procedure from MS Access. htwh,


CREATE procedure Run_Import_Raw
as

-- New SP for Import of Data - SJM 04/26/2003

EXEC msdb.dbo.sp_add_job @job_name = 'Import_Raw',
@enabled = 1,
@description = 'Acc System',
@delete_level = 1,
@notify_level_email = 3,
@notify_email_operator_name = 'AccNotice'

--AccNotice is an Operator under SQL Server...
--I then use MS Outlook to Create a Distribution List
--called AccNotice with 20 People. SQL Server has a limit.

EXEC msdb.dbo.sp_add_jobserver @job_name = 'Import_Raw', @server_name = 'Server14'

EXEC msdb.dbo.sp_add_jobstep @job_name = 'Import_Raw',
@step_name = 'ImportData',
@subsystem = 'CMDEXEC',
@command = 'DTSRUN /SServer14 /E /NImport_Raw'

EXEC msdb.dbo.sp_start_job @job_name = 'Import_Raw'

GO


Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[OhioSteve]

Wow! You clearly have jedi powers in this area.

How can you call an Sql Server stored procedure from Access?! I understand that adding this reference lets you call a DTS package:

Microsoft DTSPackage Object Library

But "DTS package" and "stored procedure" are certainly not synonyms. Are you using an ODBC connection to get to the stored procedure? If so I think that that would have some big drawbacks. If you are not using ODBC then how do you do it? The access db and the sql server db space are both on the same LAN, but not the same machine. Let's assume this:

sql server database server= myDBServer
sql server database space= myDBSpace
sql server stored procedure name= sp_myProcedure
stored procedure parameters= @var1, @var2

Given those assumptions how would you call the sp from Access?

 

[PHV]

You may consider an ADODB.Command object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[smedvid]

I agree ADO is the way to Go.

Lots of examples on the net how to call Stored Procedure from MS Access...
htwh,

http://www.databasejournal.com/features/msaccess/article.php/3407531

http://www.experts-exchange.com/Databases/MS_Access/Q_21222340.html

http://groups.google.com/group/micr...68ec3e53c4bb220?sa=X&oi=groupsr&start=2&num=3

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[OhioSteve]

I read the webpages you mentioned. Then, I made two attempts to adapt the code to my situation. Neither attempt worked, but I think that I am making progress. Details below...

 

[OhioSteve]

BUTTON HANDLER:

Private Sub Command1_Click()
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim strConn As String
strConn = "user id=XXX;datasource=SERVERNAME;initial catalog=DBSPACENAME;password=XXX"
conn.ConnectionString = strConn
conn.Open
cmd.ActiveConnection = conn
cmd.CommandText = "sp_test"
cmd.CommandType = adCmdStoredProc
cmd.Execute
Set cmd = Nothing
Set conn = Nothing
End Sub

ERROR MESSAGE:
Data source name not found and no default driver specified

WHERE IT DIED:
conn.Open

 

[OhioSteve]

BUTTON SYNTAX:
Private Sub Command2_Click()

'create objects
Dim strConn As String
strConn = "user id=XXX;data source=SERVERNAME;initial catalog=DBSPACENAME;password=XXX"
Dim conn As New ADODB.Connection
conn.ConnectionString = strConn
Dim cmd As New ADODB.Command

'configure command
With cmd
.ActiveConnection = conn
.CommandText = "sp_test"
.CommandType = adCmdStoredProc
.CommandTimeout = 0
End With

'execute stored procedure
conn.Open
cmd.Execute
conn.Close
Set cmd = Nothing

End Sub

ERROR MESSAGE:
requested operation requires an OLE DB session object, which is not supported by the current provider

WHERE IT DIED:
.activeConnection=conn

 

[OhioSteve]

I got it to work!

I changed my connection string to this...
strConn
= "default_driver=ODBC;DSN=xxx;Trusted_Connection=Yes"

Button one started to work then. I am working on introducing parameters now!

 

[smedvid]

Great... As an FYI: I try to use ADO in all cases, unless absolutely necesary.

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[OhioSteve]

Okay, thanks to your help I now know how to call a stored procedure. Now I am trying to call one WITH parameters.

MY SYNTAX (NOTICE I HAVE SHIFTED ENTIRELY TO ADO):

'create objects
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim startDateParameter As ADODB.Parameter
Dim windowsIDParameter As ADODB.Parameter
Dim strConn As String
strConn = "default_driver=ADO;DSN=[DSN NAME];Trusted_Connection=Yes"
conn.ConnectionString = strConn

'open connection
'for some reason you must open connection
'BEFORE configuring cmd
conn.Open

Set startDateParameter = cmd.CreateParameter("startDate", adDate, adParamInput)
Set windowsIDParameter = cmd.CreateParameter("windowsID", adChar, adParamInput)
startDateParameter.value = "11/27/2005"
windowsIDParameter.value = "smith"
cmd.Parameters.Append startDateParameter
cmd.Parameters.Append windowsIDParameter

'configure cmd
With cmd
.ActiveConnection = conn
.CommandText = "sp_FIRS1_1"
.CommandType = adCmdStoredProc
End With

'execute procedure
cmd.Execute

'clean-up
Set cmd = Nothing
conn.Close
Set conn = Nothing

MY ERROR MESSAGE:
Parameter object is improperly defined. Inconsistent or incomplete information was provided.

LINE IT DIES ON:
cmd.Parameters.Append windowsIDParameter

 

[OhioSteve]

Here is some relevant MSDN stuff...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcstdatatypeenum.asp

 

[OhioSteve]

Guys, I finally cracked it!! With some help from a friend IRL I made this. It really does use ado, and it calls the sql server procedure reliably.

'create some objects
Dim cmd As New ADODB.Command
Dim conn As New ADODB.Connection
Dim startDate As Date
startDate = [value]
Dim windowsID As String
windowsID = [value]
Dim connString As String
connString = [value]
Dim prmWindowsID As ADODB.Parameter
Dim prmStartDate As ADODB.Parameter
Set prmStartDate = cmd.CreateParameter("@StartDate", adDate, adParamInput, , startDate)
Set prmWindowsID = cmd.CreateParameter("@WindowsID", adChar, adParamInput, 20, windowsID)

'Open the connection.
'For some reason, you must do this prior to configuring cmd.
conn.Open connString

'configure cmd
With cmd
.ActiveConnection = conn
.CommandText = "[value]"
.CommandType = adCmdStoredProc
.CommandTimeout = 30
.Parameters.Append prmStartDate
.Parameters.Append prmWindowsID
End With

' actually execute stored procedure
cmd.Execute

' Clean up
Set cmd = Nothing
conn.Close


For the connection string, I used this...
Provider=SQLOLEDB.1;user id=[value];password=[value];Persist Security Info=False;Initial Catalog=[value];Data Source=[value]

 

[smedvid]

Glad u got the solution.. and good idea to post code... someone else can benefit from your experience...

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...