I have an access 2000 adp project with an SQL 2000 backend server, I am trying to call a stored procedure with an input and output parameter. My store procedures look like this:

Alter Procedure prcCHK
    (
        @cuid as int,
        @pts as char(10) output
    )
As
select top 1 @pts=pts from tblp1 where custid=@cuid
return

my access procedure look like

   Dim CustomerID As Integer
   Dim pts As String
   CustomerID = 3065
   Set dblocal = CurrentProject.Connection
   dblocal.Execute ("prcCHK" & "'" & CustID & "' , '" & pts & "'")

All I want the code to do is call the stored procedure "prcCHK" and for it to return the a single value 'pts'

I have checked the syntax and it work fine outside of access (SQL Query Analyser) but does not when called from the access code. Any suggestions on how I can get the stored procedure to return a single value to the calling access procedure/function.

 Dim CustomerID As Integer
   Dim pts As String
   CustomerID = 3065
   Set dblocal = CurrentProject.Connection
   dblocal.Execute ("prcCHK" & "'" & CustID & "' , '" & pts & "'")

Try to execute using the "dbo"permissions.

 Dim CustomerID As Integer
   Dim pts As String
   CustomerID = 3065
   Set dblocal = CurrentProject.Connection
   dblocal.Execute ("dbo.prcCHK" & "'" & CustID & "' , '" & pts & "'")

I too have the exact same need. When I figure this out, I'll post my solution. I have a SP working in QA but Access 2003 won't return the value from the SP output. It is like Access only can see tables/datasets and not just a "memory variable" with a value from a column, which is what the SP seems to be returning. Thanks!