Question Access to execute a Sql Stored Proc(SP)

[msaccesspatc]

THis question is under 'general' since I don't know enough to put it under macros, or vba or modules ,etc.

In my ms application, I have a form and subforms representing 3 of our server environments.. Test, Accept and PRod. I bring up a test record, make changes or do an insert. Works fine. I created a command button called "PROMOTE RECORD". The intention is to take the displayed record in Test, and promote to either the Accept environment or Prod. There is a SQL stored proc that does this perfectly, has all the logic etc.

Any one have a good article that describes If I can execute a Sql SP from Access, and shows how to do it. ?? Will I need to change the TSQL into VBA, etc or can I get it to just exec the Stored Proc(sp) passing it the parameters necessary,>>??

 

[pbaldy]

A couple of ways come to mind. Simplest may be to change the SQL of a pass-through query and run the query:

Exec StoredProcName 123, 'abc', '6/4/2015'

You can also use an ADO Command object.

 

[JLCantara]

Hello!
You will find the answer in:
https://support.microsoft.com/en-us/kb/185125
But be careful in reading it: the solution is there but the hole procedure is not exactly what you need.

Good day, JLC.

 

[msaccesspatc]

Paul and JLC, I like going for the 'simple' . The ado method is greatly appreciated to peruse and use in another situation.(makes me look smarter!!). You two are incredible for taking the time to share your knowledge. "the world IS our classroom" thanks again

 

[pbaldy]

I use both. To use the pass-through, I change the SQL of it using a DAO QueryDef (to use parameters from a form). I use a generic function from a book, but it's similar to the PassThroughFixup function near the bottom here:

http://www.sqlservercentral.com/Forums/Topic141179-131-1.aspx