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

  • Thread starter Thread starter Xav
  • Start date Start date
X

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,
 
Xav said:
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)?
Click to expand...

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
 
Back
Top