Acess Project Stored Procedure 1

[eletuw]

I have a stored procedure on SQL Server called by Access project. The stored procedure return either 0 or 1 if a login is a member of as server role.
How do I receive the returned value in Access module.
I am using the following to run the stored procedure:

CurrentProject.Connection.Execute "checkrole"

Thanks for your help.

 

[jfischer]

You can use a Command object to execute the stored procedure and retrieve the result. For example:

Dim com As ADODB.Command
Dim lRet As Long

Set com = New ADODB.Command
With com
  .ActiveConnection = CurrentProject.Connection
  .CommandText = "dbo.sp_CheckServerRole"
  .CommandTimeout = 0
  .CommandType = adCmdStoredProc
  .Parameters.Refresh
  .Parameters("@UserID") = Me!UserID
  .Parameters("@ServerRole") = "dbcreator"  'Can add DB
  .Execute
  While .State = adStateExecuting
    DoEvents
  Wend
  lRet = .Parameters(0)
End With
Set com = Nothing

If lRet = 1 Then 'Member of Server role
  '
End If

 

[eletuw]

Thanks for your help.