Run an SQL server Stored Procedure from an Access Form 2

[Fubear]

I have spent a long time trying to get one of my stored procedures on SQL server to be called from my program at startup.

The stored procedure does not return a recordset, so Access produces an error saying the procedure cannot be run.

What I did was add one line to the start of te storedprocedure:

"SELECT 0"

This makes it a select query so you can select it as the forms recordset. Any other commands in the form can come after the SELECT line and they will be executed.

If you wanted to run the stored proc from a button, you could create a new form with the proc as the recordsource to popup with a message along the lines of "Please Wait... Calculating" then close itself.

I havnt tried this myself, but my stored proc is on my main menu and runs fine on startup.

 

[chrissie1]

and doesnt

docmd.runsql "EXEC storedprocedurename"

work

Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong

 

[Fubear]

Never tried it, I havnt seen any referance to calling a stored procedure like thisbefore.

Manual just says "Double click on the stored procedure icon to run it, or put it in the record source."

 

[young]

I reseach this topic for many year now I got the right answer from the book "Access cookbook" by Ken Getz.

 

[Fubear]

Interesting - I actually have this book to hand, I cannot find this info in it (stored procedures are listed under P in the index).

Best I could find was how to pass parameters to a stored procedure set as the recordset using the inputParameters property.

 

[chrissie1]

believe me

docmd.runsql "EXEC storedprocedurename"

works
especcially in a project(.adp) Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong

 

[Fubear]

well you learn something new every day