Execute Program from DTS Package

Experts,

I have an Active X control in a DTS package that will open a Microsoft Access Database.  It works when I run it, but not as an automated job.

When I personally run this DTS package it works, because I have Microsoft Access installed on my computer.  When it is scheduled, it errors out with this message- ActiveX component can't create object: 'Access.Application'.

So I think I should install MS Access; but where (on the box where the SQL server is at)?  Do I need to set security and permission on my server to allow the service account to launch/execute Access?  This is for a large corporate environment.
LVL 1
jjrr007Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ptjcbCommented:
Good question - I have found several links that talk about how to run Access from DTS but they do not say anything about where Access should be installed.

Dim objDB
Set objDB = CreateObject("Access.Application")
objDB.OpenCurrentDatabase("c:\Report Path\database.mdb")
objDB.DoCmd.OutputTo acOutputReport, "report name", acFormatRTF, "C:\Dest Name.rtf"
objDB.CloseCurrentDatabase
objDB.Quit

Assuming you are using something like this - which explicitly looks at the C: drive (I assume it is the server because it won't know anyone else's C: drive) I would say change it to use UNC

Dim objDB
Set objDB = CreateObject("Access.Application")
objDB.OpenCurrentDatabase("\\developerComputer\C$\Report Path\database.mdb")
objDB.DoCmd.OutputTo acOutputReport, "report name", acFormatRTF, "C:\Dest Name.rtf"
objDB.CloseCurrentDatabase
objDB.Quit
jjrr007Author Commented:
The bottom active x code you wrote, is almost exactly what we have already.  Still we have the error:
ActiveX component can't create object: 'Access.Application'.

What do you suggest?  Should we install Acess on the box that houses the SQL server?  What about the permissions settings.

Thanks.  
jjrr007Author Commented:
Again, this only happens as an automated task.  Is their another way, such as transfer log-ins etc.  
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

ptjcbCommented:
ah - ok - then it may be a permission issue for SQL agent to the folder on your box. When you run it manually it works fine - but sql jobs are run under SQLAgent permissions. It may not have the necessary rights to start or run there.

You could install Access on the C: drive of the server. It would find it and usually the SQLAgent has local admin rights - it should have no issues with opening it. I do not usually allow Microsoft Office apps on a server but that's just me. I would investigate the permissions issue first, because the other solution would mean that I have to install Office on a number of servers and then maintain them...

JRossi1Commented:
Also check to see if you have different versions of Access installed...
jjrr007Author Commented:
ptcb,
Do you know the code for the Active X control to open an application instead of a database.
jjrr007Author Commented:
Let me open another question for that.

Thanks.
jjrr007Author Commented:
ptcb or other Expert,

Below is a question about opening an application from a dts package using Active X.  Kindly assist.  Thanks.
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21821442.html
jjrr007Author Commented:
ptcb,

From your last post, you said that you prefer to investigate the permissions issue first rather than installing it on the server.  My thought is that it would have to installed on the Server otherwise how could it access it using an automated job.  Is that right.  Or is their another way?

ptjcbCommented:
Sorry, I was not clear.

This depends on how much time you want to spend troubleshooting this....

It may be a permissions issue for SQLAgent (that is the "user" that runs any job) to access the directory/folders on your computer. I say that because when you run the job manually it completes with no errors (that would be under your permissions) and it opens the Access app on your server. When the job runs automatically it fails to open the Access app.

A way to do this would be to open something simple like Notepad and write a single line like "hello world" from the automatic job. If the job can open Notepad then it should open Access. If it cannot open notepad then it is a permissions issue.

This link demonstrates ways to open Notepad - http://forums.devshed.com/html-programming-1/opening-notepad-w-activex-1336.html





jjrr007Author Commented:
ptcb,

Access is not installed on the server.  Notepad definetely should be.  I am trying to determine if and how should I install Access.  Then to test if it's a permissions issue, I can try to open Notepad, if it works so will Access (when install).  
ptjcbCommented:
Yes, if you install Access and give SQL Agent the rights/permissions to work in that folder/directory - then the job should be able to open Access. I was offering the test of Notepad so that you could see if SQL Agent has the necessary permissions before installing Access.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.