Create a stored procedure from VBA in order to use DoCmd.OpenStoredProcedure with arguments

[Xav]

I would like to run a stored proc from my ADP project in my SQL server.
I want to use the DoCmd.OpenStoredProcedure in order to get the result
displayed automatically in Access. The fact is I have to 2 arguments to
pass to the sp. Is there a way to create a on the fly a sp like "CREATE
PROCEDURE spOnTheFly AS Exec mySPWithArguments arg1 arg2" and to call
DoCmd.OpenStoredProcedure spOnTheFly. Basically how can I create a sp
from VBA (ADP project)?

Thanks,

 

[Philipp Stiefel]

I would like to run a stored proc from my ADP project in my SQL server.
I want to use the DoCmd.OpenStoredProcedure in order to get the result
displayed automatically in Access. The fact is I have to 2 arguments to
pass to the sp. Is there a way to create a on the fly a sp like "CREATE
PROCEDURE spOnTheFly AS Exec mySPWithArguments arg1 arg2" and to call
DoCmd.OpenStoredProcedure spOnTheFly. Basically how can I create a sp
from VBA (ADP project)?

You can create a stored procedure by sending the necessary sql
statement over a connection to SQL-Server.

Example:

Dim strProc as String

strProc = "CREATE PROC myProc AS BEGIN SELECT 'foo' END"
CurrentProject.Connection.Execute strProc

DoCmd.OpenStoredProcedure "myProc"


Cheers
Phil