[AccessD] adodb returning error code from stored procedures

I have designed a system that uses stored procedures on SQL Server to do a rather complex (to me) 
task.  I am building a table with a specific base set of fields, then I dynamically add a new set of 
fields to that table based on selection criteria fields in a view, I append data to the table, then 
I add indexes to this table, export the table to CSV, run the CSV file through an external program, 
import a resulting CSV file back into SQL Server, dynamically build views in SQL Server, and finally 
export the resulting data back out to a flat file.

This process requires about 20-30 stored procedures.  I have been using a stored procedure that 
calls the other stored procedures but this has become unwieldy.  Plus the external program runs in 
one or several virtual machines and I need to be able to monitor the output directories on these VMs 
for the resulting output, and then execute the next set of SPs to get the data back in to SQL Server 
and finish the process.

I have been using Access to execute a subset of these stored procedures.  I run the SP that runs 
other SPs, then I switch to an Access app (I wrote) to do the export / import process, then another 
Access App (I wrote) to do the final flat file export.

Now I need to string all this stuff together and use Access VBA to make decisions on which stored 
procedures to call, how often etc., iow do it all from one place.  I understand how to do all of 
that but I am flying blind in that I do not yet know how to return a value from a stored procedure 
to Access, particularly an error if any, but also perhaps things like number of records effected and 
so forth.  In general the SPs just work, but when they do not the result is an empty file at the end 
and no clue about what specifically went wrong.  Not a good way to be working.

I am using some code provided by Charlotte (I believe)  Basically it just uses the ADODB.Command 
object to build up everything including a variable list of parameters to pass in to the SP.

My understanding is that there are two ways to get data back from a stored procedure, one is to 
declare any of the input parameters as an OUTPUT and then place values in that parameter inside of 
the stored procedure.  Another is that the SP can return a single value, kinda like a function in 
VBA.  I have fiddled and screwed around but I do not know enough about either of these things to 
actually make it happen.

I have reached the point in my control of SQL Server from Access that I need to take this next step 
and I am not finding what I need on the web to explain it.

As I said, ATM I am using a single "Generic" VBA function that allows me to pass in the name of the 
server, the database, the SP, and then a variable list of parameters.  I am reluctant to hard code a 
function for each of these stored procedures since there are so many of them and they can change as 
I discover bugs or add functionality.  OTOH I get the feeling that declaring a variable output might 
take some special syntax that will not play well with a generic "list of parameters" passed in to 
the VBA function.

Does anyone have any code, both the VBA side as well as the SP side that does this stuff?  Can 
anyone write a short but actually usable description of how to do this stuff.

I will post the generic VBA function I use in a follow up email since the code is not short.

TIA for your assistance.

-- 
John W. Colby
www.ColbyConsulting.com