[AccessD] The final piece

John,

I believe you have to use adParamOutput

http://www.eggheadcafe.com/community/aspnet/8/10032246/using-adodbparameter-obj.aspx

--
Shamil

-----Original Message-----
From: jwcolby <jwcolby at colbyconsulting.com>
To: Access Developers discussion and problem solving<accessd at databaseadvisors.com>
Date: Thu, 29 Jan 2009 13:36:54 -0500
Subject: [AccessD] The final piece

> 
> All that has gone before enabled me to create a SP which is 
> capable of returning values, and a query window that is 
> capable of calling my SP and proving to myself that the data 
> is in fact getting back out to the caller.
> 
> Now, the problem is that my VB code that calls the SPs from 
> Access does not appear to return the values.  Given that I 
> am supposed to pass by value or pass by reference 
> "correctly" when I call my SP this is not entirely surprising.
> 
> I am using a function provided by Charlotte, which was 
> probably not worrying about returning values.  the code 
> looks like this:
> 
>    Set cnn = New ADODB.Connection
>    cnn.ConnectionString = mTrustedConnection(strServerName, 
> strDatabase)
>    cnn.CursorLocation = adUseClient
> 
>    'this simplifies accessing the errors
>    'collection, which belongs to the
>    'connection
>    Set colErrs = cnn.Errors
> 
>    cnn.Open
> 
>    Set cmd = New ADODB.Command
>    With cmd
>      .ActiveConnection = cnn
>      'this could also be written as
>      'colErrs.Clear
>      .ActiveConnection.Errors.Clear
>      .CommandType = adCmdStoredProc
>      .CommandText = SPName
>      .CommandTimeout = 120
> 
>      For intLoop = LBound(Params) To UBound(Params)
> 	'gets a parameter and
>          'you have to create ALL the parameters
>          .Parameters.Append .CreateParameter( _
>                "prm" & intLoop, varPrmType, adParamInput, , 
>                                Params(intLoop))
>      Next intLoop
>      .Execute RecordsAffected:=lngRecords, 
> Options:=adCmdStoredProc
> 
> 
> 
> So, I am using the .Execute method of an ADODB.command 
> object to call a stored proc and pass it parameters.
> 
> My problem now is that I need to get a couple of parameters 
> back, i.e. they MAY (depending on the SP) be collecting data 
> from the stored procedure in order to use that data back 
> here in my VB code.
> 
> So, the first question is whether this is even possible?  If 
> I need a different function to call code that returns values 
> that is fine, but I will need to be pointed to one.
> 
> BTW, this function, provided by Charlotte, works quite well 
> to simply execute a stored procedure, as long as I do not 
> anything back from the SP.
> 
> I am getting close now, I can tell.  8-)
> 
> -- 
> John W. Colby
> www.ColbyConsulting.com
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>