Calling a DTS package/SP from MS Access

Question

Calling a DTS package/SP from MS Access

EamonSQL

SSCrazy

Points: 2307
More actions
November 8, 2006 at 7:05 am

#67804

Hi,
does anyone know how to (or have any good articles on how to ) call a DTS pakage from Access VBA. I also have to be able to call a SP on the same server.
I looked at other sites (eg aspfaq.com) but couldn't see anything.
Any help appreciated.
Eamon

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

corey lawson Hall of Fame Points: 3773 More actions · Answer 1

yes. there are some DTS dlls that you need to install and register (regsvr32 p:/ath/to/some.dll), but then you can invoke them via VBA after you then register the library.

I'm sorry I don't have the names of the files off the top of my head, but there are a few of the DLLs that you can "redistribute" to do this. You should get these from a computer that has Enterprise Manager installed on it.

yes, I've done it, but it's been awhile. You technically shouldn't install the design DLLs and EXEs, but it certainly is possible...

calling a stored procedure from Access is easy - either through DAO or ADO. That will remain to be an excercise for the reader (buy the Getz, et al. books on Access or Balmer's book).

Don't have a clue what to do with SS2005. The Business Intelligence solution tool is, imho, even more difficult to use than the DTS designer can be.

jfmccabe SSCrazy Points: 2547 More actions · Answer 2

See http://www.databasejournal.com/features/mssql/article.php/1459181

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=568

and then BoL on the procedures used.

To call the stored procedure from Access use an ADODB command object - see the help files.

One buglet, you need fairly high privileges to use this.

EamonSQL SSCrazy Points: 2307 More actions · Answer 3

Thanks Guys for the guidance

you led me to this link which done the trick

http://support.microsoft.com/kb/252987

Eamon