[AccessD] The final piece

jwcolby jwcolby at colbyconsulting.com
Thu Jan 29 12:36:54 CST 2009


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



More information about the AccessD mailing list