[dba-SQLServer]Execute DTS from VBA

Mark,

Your code worked!  I just made a really simple DTS to test it out with.
It seems to work about 75% of the time.  I'm not at all sure what is
different the other 25% of the time.  It may be the quick succession
thing that you mentioned.  I'll need to play with this a bit more.  

A couple of questions:

1.  What references do you have set for DTS?  I just set
MicrosoftDTSPackageLibrary.

2.  What does "DTSSQLStgFlag_UseTrustedConnection" do? And, related to
that, what do I need to consider about permissions?  I want users to be
able to execute the DTS, but they don't have SA access to the Server the
way I do.  

On a OT note, I just got back from 9 days of honeymooning in Ireland.
What a beautiful country.  We spent most of our time in County Donegal,
but saw a lot of the west coast on our drive up from Shannon to Donegal.
Where do you live?

--Susan


-----Original Message-----
From: Mark L. Breen [mailto:subs at solution-providers.ie] 
Sent: Thursday, June 05, 2003 3:25 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer]Execute DTS from VBA


Hello Susan,

Have a look at the following lines of code

            Dim objPackage2 As New DTS.Package
            objPackage2.LoadFromSQLServer ".", , ,
DTSSQLStgFlag_UseTrustedConnection, , , , "NEWSPPSPackage"
            objPackage2.FailOnError = True
            objPackage2.Execute
            objPackage2.UnInitialize
            Set objPackage2 = Nothing


They are taken from a system that I have just finished, they are
working. It is important to use the uninitialize, otherwise, you cannot
reuse the package.

One word of warning, I am sometimes finding that it does not like being
used in quick sucession, but only sometimes, I cannot figure out why if
fails when it does.  Other times, it will import five and ten files
rapidly in quick sucession.

I was considering rewriting the code to manually handle the file that I
am importing on a line by line basis, but I feel that DTS should be the
way to go, otherwise why would MS have created it in the first place.
But then again, you could say that about the fax facility in Windows 95.

Let me know what you decide to do, I would be interested.

Thanks

Mark Breen
Ireland




----- Original Message ----- 
From: "Susan Geller" <sgeller at cce.umn.edu>
To: <dba-SQLServer at databaseadvisors.com>
Sent: Wednesday, June 04, 2003 2:22 PM
Subject: [dba-SQLServer]Execute DTS from VBA


> Can I execute a DTS from inside my ADP file in code?  How?
>
> Access XP, Windows XP, SQL Server 2000.
>
> Thanks.
>
> --Susan
>
>
> Susan B. Geller
> Office of Information Systems
> College of Continuing Education
> University of Minnesota
> 306 Wesbrook Hall
> 77 Pleasant Street SE
> Minneapolis, MN 55455
> Phone:  612-626-4785
> Fax:  612-625-2568
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com