Using DTS to print report in Access

[lendman]

I've been trying to use the following VB script as ActiveX DTS package to print a report in Access ADP. When I run this, I get an error "Microsoft Access can't open the database because it is missing or opened exclusively by another user." The path indicated for the db is from the same computer running SQL. Any ideas what's I'm doing wrong?

Thanks!

Function PrintReport()
Dim objDB
Set objDB = CreateObject("Access.Application")
objDB.OpenCurrentDatabase("C:\AccessDB\MyDB.adp")
objDB.DoCmd.OpenReport "rptTemp", 0
objDB.CloseCurrentDatabase
objDB.Quit
PrintReport = DTSTaskExecResult_Success
End Function

 

[Catadmin]

Close Enterprise Manager and Query Analyzer, then try to run it. If you're still getting that error, make sure there are no other linked servers using the DB or that there are no other users connected and try again.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[lendman]

Do you mean close Enterprise Manager and then reopen it. Otherwise, how can I run package unless I do it from Enterprise Manager? I've tried using another database that is a mdb file with definetely no other users connected and the same problem occured. Could the issue be related to the fact that "C:\directoryname" from sql's perspective, when it tries to find the db location is not the same as what appears to me? Could there be any user issues on the computer? Is there any way to debug this - by putting the db directly in a sql directory so I don't have to specify a path at all? I really want to find a solution here so I'm just trying to think of any creative solution.

Thanks!

 

[Catadmin]

Try scheduling the package for five minutes in the future, then close Enterprise Manager down. Closing EM doesn't stop SQL Server, but it does close off a connection.

See if it runs like that.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[lendman]

Thank you for this suggestion. It didn't work but I've got more information. From the job log, there are more details about why the procedure failed. It's a vbscript runtime error - permission denied on 'CreateObject' (see below): Any idea what to do about this? Thanks very much!

Error string: Error Code: 0 Error Source= Microsoft VBScript runtime error Error Description: Permission denied: 'CreateObject' Error on Line 6 Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4500 Error Detail Records: Error: -2147220482 (800403FE); Provider Error: 0 (0) Error string: Error Code: 0 Error Source= Microsoft VBScript runtime error Error Description: Permission denied: 'CreateObject' Error on Line 6 Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4500 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

 

[Catadmin]

What object is the job trying to create? A connection object or a table?? Check your code. If it's a connection object, then open up Enterprise Manager.

Check the permissions for the Login your connection string is using. Go to the database in question, open it up and navigate to USERS. Right click the User name and navigate to All Tasks -> Manage Permissions. Here is where you will find individual object permissions.

Then go to Security -> Logins, right click the Login and verify the Server and Database Roles this login has. If there is no login in this folder, then you need to create it and grant it permissions on the database. If there is no USER on the database, but there is a login under Security, grant the login permissions on the database.

If the object it is trying to create is on the Access / Client side, you need to verify your user has permisions on the file share.

Does that help any?



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[lendman]

This is about using DTS to run a report in Microsoft Access... so it's not a SQL database that you would access in Enterprise Manager. I'm just using a DTS package as shown above. I have made sure that the MS Access database file permissions give access to Sql to no avail.

 

[yksvaan]

Have you tried VBScript or Access forums ?

BTW, how about running that ActiveX from command prompt in a vbs file (if it's possible)?

Could it be something like Dim objDB As Access.Application

Cheers


[blue]Backup system is as good as the latest recovery[/blue]