ActiveX can't create object error?

[patrichek]

Hello,

when using my pc with EM installed i can run this code from my access db just fine, but when logged in as a domain user on a different pc i get an activex can't create object error.
I've checked the sql server permissions and have given domain users privlidges for that database. I've also made sure that dtspkg.dll is referenced in VB.

Users have no problem running other VB codes.....?

Any ideas?

Private Sub SendCmd_Click()
Dim dtsp As New DTS.Package
dtsp.LoadFromSQLServer _
        ServerName:="The-server", _
        ServerUserName:="user", _
        ServerPassword:="*****", _
        PackageName:="TheDTSPackageName"
dtsp.Execute
End Sub

thanks

 

[AlexCuse]

Hey Pat,

try this (I was wrong about the double quotes)

Public Function DTS()

Dim SV As Variant
Dim DQ, s, s1, s2, s3 As String
Dim ServerName As String
Dim PackageName As String
Dim ExecutionLine As String

DQ = """"
s = "dtsrun"
s1 = "/S"
s2 = "/E"
s3 = "/N"
ServerName = "MyServer77569"
PackageName = "DTSTEST"
ExecutionLine = s & " " & s1 & ServerName & " " & s2 & " " & s3 & PackageName

Debug.Print ExecutionLine

SV = Shell(ExecutionLine)

End Function

(actually tested)

I am glad that you got me motivated to figure this out. Here is a valuable resource that should help you to get the password and everything incorporated into your "ExecutionLine" for your non-EM users (hope you can map them to SQL server drive):

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_dtsrun_95kp.asp

You should only need to add mapping to the dtsrun utility (ie x:\SomeDir\dtsrun.exe) and the command switches for username and password to your "ExecutionLine" in the appropriate places.


Hope this helps,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[unclerico]

the dll is not registered. are you sure that the dts components are loaded on the machine in question? if you go into references and you look at the properties of the dtspkg.dll, where does it say it is registered? you could always try the following from a command prompt (be aware that you may need to alter the path as needed):

regsvr32 "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtspkg.dll"

 

[AlexCuse]

of course you can remove DQ and the debug.print command. Sorry about that, I was kind of throwing that thing together on the fly.

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[patrichek]

seems like there should be a better way than having to map a drive for dtsrun.exe?


I didn't quite understaned unclerico's post? i know how to register dlls but his example is for a system with EM on it, correct? or am i missing something?

 

[patrichek]

I'll probably try this thread:

http://tek-tips.com/viewthread.cfm?qid=1187673

i'm not too excited about mapping drives and so forth

thanks!

 

[AlexCuse]

You only need to enable some kind of login script that gives the user access to a directory that holds the dtsrun utility. It could even run on opening the application. I really like the thread you posted though, that is slick. Let me know how it works.

Thanks,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[patrichek]

I wonder if i dumped the dtsrun into the netlogon folder then it should run without mapping...?