How to interogate return codes from stored procedure in SQL

[Wilciek]

I would like to run a pass through query from Access which
will Exec a stored procedure (which performs various updates etc to tables) in a SQL baack end. I want to set the return codes for errors after each step in the procedure. I know how to do the first bit but I would like to know is how do I interogate the return code from vba procedure which instigated the query in the first place. Any ideas would be welcome.

Thanks in anticipation

 

[PHV]

What is your actual VBA code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Wilciek]

It would be similar to this as I haven't written it yet but I am unsure how to incorporate the return code which I would have to set in the exec statement below-

Function fnExtractSelection(stExtractName As String)

Dim qdf_mle_Extracts As QueryDef

' Create a QueryDef object to retrieve
' data from the datawarehouse SQL Server database(BIS).
Set qdf_mle_Extracts = CurrentDb.CreateQueryDef("qExtractSelection")
With qdf_mle_Extracts
.Connect = "ODBC;DATABASE=BIS;UID=sms;PWD=sms;DSN=BIS"
.SQL = "EXEC mle_Extracts " & "'" & stExtractName & "', " & iRunYear & ", " & iRunPeriod
End With

CurrentDb.Close

End Function

 

[PHV]

Your code don't launch any SQL at all.
Why not using an ADODB.Command object ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Wilciek]

Sorry - the code that calls the function is as follows
--------------------------------------------------------
fnExtractSelection "Product Weightings"

fnTransferText Me!RunFolder & "CPWT", "CPWT", "qfrmExtractProductWeightings"

'qfrmExtractProductWeightings' uses the query generated by the fnExtractSelection to transfer the text.

However I want to execute a new procedure from within access and be able to check the return code to see if it has failed. This procedure I am going to write will update 2 or 3 tables and I want to know if the procedure does fail so that I can display a message on the user's screen