[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

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.
0
jjrr007
Asked:
jjrr007
  • 7
  • 4
1 Solution
 
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
0
 
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.  
0
 
jjrr007Author Commented:
Again, this only happens as an automated task.  Is their another way, such as transfer log-ins etc.  
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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...

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

Thanks.
0
 
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
0
 
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?

0
 
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





0
 
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).  
0
 
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.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now