SQL Stored Procedure return values

How do i get the return value from a stored procedure, I know the sql is:

exec @ret = sp_check

but i would like to get the return value so that access can respond to it.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

what we do is return the code via a select and store the results in a resultset. I don't beleieve DAO can handle return values from stored procedures. ADO can but that would mean adding an ado reference to you Access code.
simonsabin is partially correct.  However, without adding any new references, you can use ODBCDirect to behave "similar" to ADO in that you can pass output parameters and then see them come back.

For example (this is borrowed heavily from "SQL Server 7 Developer's Guide"):
Private Sub CallQDsp()
    Dim qd As QueryDef
    Dim sConnect As String
    Dim sSQL As String, sServer As String
    Dim sLoginID As String, sPassword As String
    Dim cn As Connection
    Dim ws As Workspace
    sLoginID = "sa"
    sPassword = vbNullString
    sServer = "DAREK"
    'The type parameter sets the workspace to ODBCDirect
    Set ws = DBEngine.CreateWorkspace("NewODBCDirectWS", sLoginID, _
                sPassword, dbUseODBC)

    'Set the default Workspace type to ODBCDirect
    DBEngine.DefaultType = dbUseODBC
    'Define the DSN-less connection
    sConnect = "ODBC;Driver=SQL Server;UID=" & sLoginID & _
        ";PWD=" & sPassword & _
        ";Server=" & sServer & _
    'Open the new connection
    Set cn = ws.OpenConnection("", dbDriverComplete, False, sConnect)
    'Create the new procedure
    sSQL = "Create Proc CountStateRows " _
        & "(@state char(2), @rows Int Output) As " _
        & "Select @rows = Count(*) From authors Where state = @state"
    cn.Execute sSQL
    'Call the procedure
    sSQL = "{ Call CountStateRows (? ,?) }"
    Set qd = cn.CreateQueryDef("", sSQL)
    qd.Parameters(0).Value = "UT"
    qd.Parameters(1).Direction = dbParamOutput
    MsgBox "Number of rows: " & qd.Parameters(1).Value
End Sub

Obviously if you do use something like this you will want to make sure you cleanup your variables...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Thats output parameters not return values. very different.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Sorry, I misread the question...
SadManAuthor Commented:
Ethier way is OK, I can eaily re-write the stored proc, I think...

I'm going to try your answer dtomyn.
SadManAuthor Commented:
Thanks dtomyn, not exactly what I was after but changing the code allowed me to get the results.

It worked perfectly.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.