[AccessD] Excecuting SP in SQL db using ADO

Hi,

I haven't been following the conversations  / activity here lately ... and
now opening up the gmail account I've created just for the Accessd
discussion group I see all the familiar names ... I have been hanging out on
this discussion group for a number of years - contributed bery little - but
learned a lot .... so let me just take this random moment to thank each and
everyone on this list ...

No on to today's puzzle: I want to return back into my VBA code the return
value that I can somehow control is coming out of a SQL 2005 Stored
Procedure .... The SP is non row returning:  it checks if a record exists
and inserts one if the record does not exist. The return value of the SP is
normally 0 - I've set my own return values based on the outcome of the
record checking....

Now, how can I pick up the return value in vba ??

More specific is there a built in ADO 'something' that will return the
return code??
Something I can call on the line (see below for full function)

    cn.Execute strSP, lngRecsAffected, adExecuteNoRecords
In the code below you see I am using the lngRecsAffected to signal success
in executing the SP as it will always return -1 .
If there is a timeout or other exception, the SP will not commit and I
return to the calling code that the SP did not commit and  what the SP / SQL
string was ...

Rather than picking up the lngRecsAffected I want to pick up the returncode
from the SP.

Can I do that using ADO in an immediate sense??

I've been looking at the CADOConnSQLServer class module from Total Visual
SourceBook 2002 - is that the direction to go?

If so does any one have a example to start from?

... sigh .... I really should be jumping to .net - don't even mention it!

Regards
borge


Public Function fncExecuteSP(ByVal strSP As String) As String
On Error GoTo EH:
'!--------------------------------------------------------------------------
'!Purpose      : Generic code for executing an SP or sql string in the SQL
Db backend
                 'use for non row returning SPs and sql strings
'!--------------------------------------------------------------------------
'NOTE: The calling code is responsible for providing a fully parameterized
'pass through query or the name of an SP with all parameters set

    ' Set connection string
    ' Assuming use of static functions for setting connection parameter
values
    ' for use throughout the application
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim lngRecsAffected As Long
    lngRecsAffected = 0

    Set cn = New ADODB.Connection
    With cn
      .Provider = "Microsoft.Access.OLEDB.10.0"
      .Properties("Data Provider").Value = "SQLOLEDB"
      .Properties("Data Source").Value = CurrentSQLOLEDB_DataSource
'Current... etc are static functions
      .Properties("User ID").Value = CurrentODBC_UID
      .Properties("Password").Value = CurrentODBC_PWD
      .Properties("Initial Catalog").Value = CurrentSQLOLEDB_InitialCatalog
      .Open
    End With

  On Error GoTo ETransaction
    cn.BeginTrans
    cn.Execute strSP, lngRecsAffected, adExecuteNoRecords
    cn.CommitTrans
    fncExecuteSP = lngRecsAffected

EDone:
    On Error Resume Next
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
EX:
    DoCmd.SetWarnings True
    Exit Function
ETransaction:
        cn.RollbackTrans
        fncExecuteSP = "RMDS: Stored Procedure transaction was rolled back!
Attempted to execute:[" & strSP & "]" & vbCrLf
        Resume EDone
EH:
      fncExecuteSP = "RMDS: " & fncExecuteSP & " Error in fncExecuteSP " &
": " & Err.Number & " " & Err.Description & vbCrLf & "Error occurred while
trying to execute: [" & strSP & "]." & vbCrLf
  Resume EDone
End Function