Using DTS from Access VBA module 1

[nuVBer]

I have an Access 2000 db where I am trying to use the DTS functionality of SQL Server to run a DTS package. It works fine on my pc, I suppose because I have SQL Server on my machine. But when someone else trys to run the VBA module that runs the DTS.Execute function it won't work since they don't have SQL Server on their machines. I think it requires some .dll's in order for it to run on their machine. Is their an easy way to distribute these? Would it be possible to run the DTS package from a stored procedure that could be kicked off from the Access VBA module? Can a stored procedure run a DTS package?

 

[JoshuaBloom]

Im not positive if this will work, but usuallly the MS MDAC executable will install most of the needed data access .dll's

You can find it at

http://www.microsoft.com/downloads/...5f-c2e0-471d-a33b-82ad1d514737&DisplayLang=en

Josh

 

[nuVBer]

This does have a bunch of the data access files, but not the DTS dll's that are required to make it operate. I did figure out a work-around though. Thanks for your help anyway.

 

[tEkHEd]

nuVBer

Could you possibly post what your workaround was? or let me know the library that includes the DLL's for DTS commands.

Thanks.

 

[nuVBer]

Yes, sorry. I just put the DTSRun command in a stored procedure on the SQL Server and execute the stored procedure from access. That way the DTS package runs straight from the SQL Server and no files are needed on the local machine to run it.

 

[tEkHEd]

Well I have found a way to run the DTS package when you install:

Microsoft DTSPackage Object Library

But I would like to know if I can use a DTS.ExecutePackage with a different reference that can be distributed with the DB as this one comes from the install of the client side tools of SQL2000 (EM)

Any one know of anything that will do? as I can't really go round and install EM on 20 peoples boxes..!! :¬/

 

[Jimmy211]

nuVBer,

I know its been a long time since you posted this thread, but I have the same problem of trying to execute a DTS package on the server. Would you be willing to post the code to the stored procedure that executes the DTSRUN utility? I've searched BOL to no avail on how to do this.

Thanks,
Jimmy

 

[nuVBer]

Yes, I looked it up and it is pretty simple really. Here it is:
CREATE PROCEDURE [essbase].RunDataTS AS

exec ("master..xp_cmdshell 'dtsrun /Smerrick /Uessbase /Pessbase /N Load oper-accrl.txt file'")
GO