[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