[AccessD] adodb returning error code from stored procedures

Fred Hooper fahooper at gmail.com
Thu May 14 10:51:10 CDT 2009


Not a solution but a workaround: When I've had a similar problem I create a
Messages table in the db and have the procedure append a message to that
table. My app just looks at that table periodically and responds
accordingly.

Fred

On Thu, May 14, 2009 at 11:03 AM, jwcolby <jwcolby at colbyconsulting.com>wrote:

> 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
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



More information about the AccessD mailing list