Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2012
    Posts
    2

    Unanswered: Call SQL stored proc and return output param from Access

    What is the easiest way to do this?

    From SQL 2005 or 2008 RS:
    -------------------------------------------------------
    CREATE PROCEDURE [dbo].[__getPI]
    @T REAL OUTPUT
    AS
    SET @T = PI()
    -------------------------------------------------------
    From MSAccess 2007 or 2010:

    1. Create a new form (Form1), a new button (Button1), a new text box (TextBox1).
    2. Make use of passthru queries, VB, or whatever...
    3. Use the Button1 to call the "__getPI" stored procedure and capture the value of @T and put it in TextBox1.
    4. TextBox1 should now have a value of 3.1416.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's an example using the ADODB Library (it's possible to write a more compact version):
    Code:
    Private Sub Command_GetPI_Click()
    
        Dim cnn As ADODB.Connection
        Dim cmd As ADODB.Command
        Dim prm As ADODB.Parameter
        
        Set cnn = New ADODB.Connection
        Set cmd = New ADODB.Command
        Set prm = New ADODB.Parameter
        cnn.ConnectionString = "driver={SQL Server};SERVER=Sandbox_2;DATABASE=Scratchpad;Trusted_Connection=Yes;"
        cnn.Open
        cmd.ActiveConnection = cnn
        cmd.CommandType = adCmdStoredProc
        cmd.CommandText = "__GetPI"
        prm.Name = "Pi"
        prm.Type = adDouble
        prm.Direction = adParamOutput
        cmd.Parameters.Append prm
        cmd.Execute
        Me.Text_PI.Value = prm.Value
        cnn.Close
        Set prm = Nothing
        Set cmd = Nothing
        Set cnn = Nothing
    
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Jan 2012
    Posts
    2

    access to sp

    Got it working using something 99% similar to your code, right before your reply. I should have post a question sooner, it would have saved me a lot of time. Thanks for sharing Sinndho.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •