OpenStoredProcedure

[Guest]

I'm trying to use OpenStoredProcedure to execute a SQL query. Below is a
snippet of the relevant code.

stDocName = "sp_PXReport"
DoCmd.OpenStoredProcedure stDocName, acViewNormal, acEdit

Fairly straight forward, the user clicks on a form button that then executes
this event procedure, except that it works for some people and it does not
work for others. The error message that pops up says that it cannot find the
object "sp_PXReport". However, if I have the user go to the queries section
of Access and double click on the stored procedure itself, it executes, which
to me means that it isn't a SQL side permission (and I did doublecheck the
permissions settings anyway, just in case, the users do have EXECUTE granted
to them on that stored procedure), but I don't know what else it could be.

Anyone have any other ideas?

Thank you, Sumi

 

[Sylvain Lafontaine]

First, you shouldn't use the prefix « sp_ » for you own stored procedures.
It has a special signification under SQL and may well lead to this kind of
problem; see the BOL for more detail about this. Beside this point, here
are two suggestions that I didn't take the time to test:

1- Make sure that all logins on the SQL-Server have the same default
database or that it is specified in the connection string.

2- Add the prefix dbo. to the name of the procedure, like in «
stDocName="dbo.sp_PXReport" »

Finally, maybe you should use ADO objects instead of the
DoCmd.OpenStoredProcedure. I'm not really sure about the usefullness of this
strange thing that is the DoCmd.OpenStoredProcedure.

S. L.

 

[Guest]

Option #2, adding dbo.xxxxx fixed the problem.

Thank you very much,

Sumi Lee