[AccessD] Stored procedures as Queries

John,
After testing my own code, I found that it didn't work as advertised. I hope
you didn't waste any time with it.
Here's some sample code that really does work. It's an excerpt from a sproc
that takes in a tablename as an input, and returns which SQL database(s)
that  table is found in.
----------------------------------------------------------------------------
--------------------------------------------
Sproc  returns a recordset, a text value, and a status code (which I'm using
here to return a count)...
	< a bunch of T-SQL code>
	SELECT * FROM @DBList; --returns output as a recordset 
	SET @OutVal = @DBNames; --returns output as a concatenated string
	RETURN @NumDBs --returns number of databases in which table was
found  
	END

a) Access VBA Code executes the sproc and reads the recordset...
    	Set rOutput = ADOCommand.Execute
    	RecordsetOut = Null
   	 If Not (rOutput.BOF And rOutput.EOF) Then
        	Do While Not rOutput.EOF
           		RecordsetOut = IIf(IsNull(RecordsetOut), "",
RecordsetOut + "; ") + rOutput.Fields(0)
           		rOutput.MoveNext
       	Loop
b) Access reads the output string...
   	 OutVal = ADOCommand.Parameters(OutputParmName).Value
c) Access reads the return status, which is always in the first parameter...
    	ReturnStatus = ADOCommand.Parameters(0).Value

Debug.Print "RetVal = " & RetVal & vbCrLf & "OutVal = " & OutVal & vbCrLf &
"RecordsetOut = " & RecordsetOut

RetVal = 2
OutVal = [CRMA Central]; [CRMA_Import]
RecordsetOut = [CRMA Central]; [CRMA_Import]	

Here are a couple of gotchas: 
If you execute the sproc with this command [Set rOutput =
ADOCommand.Execute] but don't read the recordset, this is the result ...
RetVal = 0
OutVal = 
RecordsetOut =

If you execute the sproc with this command [Set rOutput =
ADOCommand.Execute] and read the output values (steps b and c) before the
recordset (step a)...
RetVal = 0
OutVal = 
RecordsetOut = [CRMA Central]; [CRMA_Import]

However, if you don't need to read the recordset, execute the sproc with
this command [ADOCommand.Execute] and you'll get the correct output...

a) Access VBA Code executes the sproc...
    	ADOCommand.Execute
b) Access reads the output string...
   	 OutVal = ADOCommand.Parameters(OutputParmName).Value
c) Access reads the return status...
    	ReturnStatus = ADOCommand.Parameters(0).Value

Debug.Print "RetVal = " & RetVal & vbCrLf & "OutVal = " & OutVal & vbCrLf &
"RecordsetOut = " & RecordsetOut

RetVal = 2
OutVal = [CRMA Central]; [CRMA_Import]
RecordsetOut =

HTH,
Darrell

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darrell Burns
Sent: Saturday, August 06, 2011 8:23 AM
To: 'Access Developers discussion and problem solving'
Subject: [Spam]8.51 Re: [AccessD] Stored procedures as Queries

Hi John.
My preference is to let the stored procedure do all the work and then pass
back either a value or a recordset to Access. 
Try this...

            Dim ADOCommand As New adodb.Command
            Dim ADOParms As New adodb.parameter 'one row per input variable
required by the sproc
            ADOCommand.ActiveConnection = SQLConnect 'connection string
            ADOCommand.CommandText = "sproc" 'stored procedure name
            ADOCommand.CommandType = adCmdStoredProc
            Dim InVal As Variant 'a value passed to the stored procedure
            Dim ReturnType As Integer '0=single value, 1=recordset
            If ReturnType = 0 Then
                Dim RetVal As Long 'if the sproc returns a single value
            Else
                Dim rReturn As New adodb.Recordset 'if the sproc returns a
recordset
            End If
            'append Input parameter(s) needed by the sproc...
            Set ADOParms = ADOCommand.CreateParameter("InputParmName",
adVarChar, adParamInput, 200, InVal)
            ADOCommand.Parameters.Append ADOParms
            'append an Output parameter if the sproc produces a return
value...
            Set ADOParms = ADOCommand.CreateParameter("OutputParmName",
adVarChar, adParamInput, 200, "OutputParmName")
            ADOCommand.Parameters.Append ADOParms
            ADOCommand.Execute
            If ReturnType = 0 Then
                'execute this command if the stored procedure returns a
single value...
                RetVal = ADOCommand.Parameters("OutputParmName").Value
'Return value
            Else
                'execute this command if the stored procedure returns a
recordset...
                Set rReturn = ADOCommand.Execute
            End If

HTH,
Darrell


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, August 05, 2011 8:58 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Stored procedures as Queries

I am using pass through queries where I reference a stored procedure and
pass a param through.  At the moment I am having to open the querydef and
replace the parameter with some value, save the sql of the querydef and then
save the querydef.  Is there any other way to do this?  It seems crude to
the max.

--
John W. Colby
www.ColbyConsulting.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com