Return value indicating update success/failure of SQL Server stored procedure via ADO/VBA

Question

I have a SQL Server 2008 stored procedure that updates values in a table. I would like to have the stored procedure return an integer value indicating that the update was successful (return 0) or not (returns error number). What would be the best way to accomplish this via ADO and VBA? Here some of my code in simplified form that performs the update ... I am just not sure how to get back the return value of the stored procedure

Public Function DoUpdate(employeeID as integer, firstName as string, lastname as string) as integer

  Dim cnn As ADODB.Connection
  Dim cmd As ADODB.Command
  Dim activeConnectionString As String

  activeConnectionString = GetActiveConnectionString()

  Set cnn = New ADODB.Connection
  cnn.ConnectionString = activeConnectionString
  cnn.CursorLocation = adUseClient
  cnn.Open

  Set cmd = New ADODB.Command
  cmd.ActiveConnection = cnn
  cmd.CommandType = adCmdStoredProc
  cmd.CommandText = "uspUpdateEmployeeName"
  cmd.NamedParameters = True

  cmd.Parameters("@EmployeeID").Value = employeeID 
  cmd.Parameters("@FirstName").Value = firstName
  cmd.Parameters("@LastName").Value = lastName

  cmd.Execute

  'Unsure of how to get back return value here
  'DoUpdate = returnValue

  Set cnn = Nothing

End Function

Answer 1

If you use

  cmd.Execute lngRecs

lngRecs should contain records affected.

Answer 2

I seem to remember that you need to supply an extra parameter with the type 'adParamReturnValue' like this:

Dim lRetVal as Long

Set cmd = New ADODB.Command
cmd.Parameters.Append .CreateParameter("returnvalue", adInteger, adParamReturnValue)

cmd.Execute

'Now check the return value of the procedure
lRetVal = cmd.Parameters("returnvalue")

If lRetVal > 0 then