How do you execute a SQL DTS???

[Matthew Wells]

Hello.

How do you execute an existing SQL DTS package from a command button on an
Access form. I am not going to use Access data pages, just a standard form.

Thanks.

Matthew Wells
MW****@FirstByte.net

[Rich P]

You can save a DTS package down to a VB6 file from the DTS wizard in Sql
Server 2000. Access alone does not have enough horse power to run the
DTS package code by itself. Your best bet would be to import the DTS
package module to a vb6 (or vb.net) project and compile that into an
exe. Then you can use the Shell function to invoke the exe you created.

Dim RetVal As Variant
RetVal = Shell("C:\someDir\MyDTSPkg.exe")

If you have several tables you need to add data to in sql server, say 4
tables (because I had such a project) you will have 4 different
packages. I turned each package into a class module so that I could use
an Interface construct (in vb.net - can't do this with a vb6 Interface
because this procdedure uses inheritance - maybe you could but won't
work as reliably from vb6) where I could loop through each package
class. Looks something like this:

Public Interface ifDTS 'I added this Interface declare RunDTS
'to the first class module
End Interface

Public Class clsDTS0 'first DTS package class
Implements ifDTS 'note: RunDTS is alias for runDTS0()
Public Sub runDTS0() Implements ifDTS.RunDTS
...dts code here
End Class

Public Class clsDTS1 '2nd DTS package class
Implements ifDTS 'note: RunDTS is alias for runDTS1()
Public Sub runDTS1() Implements ifDTS.RunDTS
...dts code here
End Class

Public Class clsDTS2 '3rd DTS package class
Implements ifDTS 'note: RunDTS is alias for runDTS2()
Public Sub runDTS2() Implements ifDTS.RunDTS
...dts code here
End Class
Then in the Main Form Class module call the interface like this:

Private Sub RunAllDTS()
Dim i As Integer
Dim dts(2) As ifDTS 'create array of DTS packages
'here is where you are using inheritanc
'I think
dts(0) = New clsDTS0 1st package class
dts(1) = New clsDTS1 '2nd package class
dts(2) = New clsDTS2 '3rd package class
For i = 0 To Ubound(dts)
dts(i).RunDTS 'here is where the RunDTS alias comes in
Next
...
End Sub

This may seem like a little overkill for 3 DTS packages, but I had to
call each package in my project several times because I was reading
several delimited textfiles into sql server for each DTS package. Note:
these were several very large textfiles like 27 megs per text file,
about 55 of them. DTS way faster than using ADO.Net Fill method
(although ADO.Net2 has a SqlBulkCopy Class in VS.Net2005 that does what
DTS does, but still in Beta). But for runnning DTS from Access, this is
how I would do it.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Rich P]

The Interface declaration got messed up on my first post. Here is the
Interface declaration again, corrected

Public Interface ifDTS 'I added this Interface declare
Sub RunDTS 'to the first class module
End Interface

Here is the declaration again without the notes

Public Interface ifDTS
Sub RunDTS
End Interface

Sub RunDTS is the alias for the subs in all the Classes. These Subs
could all be named the same or have different names. When you add each
class to the Interface, you Implement the alias, RunDTS. You have to
say
-->Implements ifDTS<-- right underneath the class declaration and then
for the sub you will reference in the Interface you have to say

-->Public Sub x() Implements ifDTS.RunDTS<--

where RunDTS is the alias for Sub x()

Here is the sample again without comments:

Public Interface ifDTS
Sub RunDTS
End Interface

Public Class clsDTS0
Implements ifDTS

Public Sub runDTS0() Implements ifDTS.RunDTS
Dim ifDTS.RunDTS
...dts code here
End Class

Public Class clsDTS1
Implements ifDTS
Public Sub runDTS1() Implements ifDTS.RunDTS
...dts code here
End Class

Public Class clsDTS2
Implements ifDTS
Public Sub runDTS2() Implements ifDTS.RunDTS
...dts code here
End Class
Then in the Main Form Class module call the interface like this:

Private Sub RunAllDTS()
Dim i As Integer
Dim dts(2) As ifDTS

dts(0) = New clsDTS0 1st package class
dts(1) = New clsDTS1 '2nd package class
dts(2) = New clsDTS2 '3rd package class
For i = 0 To Ubound(dts)
dts(i).RunDTS
Next
...
End Sub

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!