Those of you who have ever done any serious programming with SQL Server stored procedures will know that they return an integer value to their caller on completion. The default value is ‘0’, which indicates success, and any other value indicates a failure at some stage in its processing. VBA has the power to capture these return values through the ADODB.Command object but, in order to do so, there are a few points you should consider.
1. Invoke Parameters.Refresh before Execute.
2. Specify adCmdStoredProc as the CommandType.
3. Specify an OLE DB provider but don't use MSDASQL.
4. If the Stored Procedure consists of more than one statement (most do) make sure it starts with SET NOCOUNT ON.
5. Poke/prod any returned, open recordset before examining the Return Value.
6. Use a TRY..CATCH block in your Stored Procedure to handle exceptions.
7. Use additional error handling in your VBA code.
This single line of code can saves you a significant amount of work. When it’s invoked it does a round trip to the SQL Server database and gets metadata about the stored procedure’s parameters. You then only need to set the values of any input parameters. You don’t need to Create the parameters and you don’t need to Append those (otherwise created) parameters.
Parameters.Refresh only works when you explicitly set the CommandType to adCmdStoredProc. With any other CommandType, it will not silently do nothing. Your code won’t break until you actually try to operate on the items in the Parameters collection.
MSDASQL (a.k.a the “Microsoft OLE DB Provider for ODBC Drivers”) is an old OLE DB provider. It doesn’t support Parameters.Refresh and you have to close the returned recordset in order to capture the return value. While the former can be sidestepped by explicitly creating and appending Parameters prior to Execute, the latter is an insurmountable problem for stored procedures that execute INSERT, UPDATE, DELETE or MERGE statements only, ie. what Access Developers call “Action” queries. In such cases, a closed recordset is returned from Execute. As you can’t close a recordset that is already closed, there is no way to observe the return value.
Simply put: don’t use the MSDASQL Provider.
It is better to specify one of the SQLNCLIs as your Provider or, if you don’t have them installed, use SQLOLEDB which is installed with MS Office. These providers can be used with an ODBC Driver, but don’t make the mistake of not specifying a Provider in the connection string as ODBC will default to the MSDASQL if you do.
SET NOCOUNT ON
When a stored procedure processes a DML statement that alters the database (eg. INSERT, UPDATE, DELETE or MERGE) it returns to the calling client a message packet entitled “DONE_IN_PROC”. If the client is SQL Server Management Studio (SSMS), this message is interpreted and the number of rows affected by the statement is displayed, e.g.:
9 row(s) affected
ADO, however, processes this message packet differently. From MSDN (emphasis added):
If the command is not intended to return results (for example, an SQL UPDATE query) the provider returns Nothing as long as the option adExecuteNoRecords is specified; otherwise Execute returns a closed Recordset.
This results in inconsistent behaviour. If your stored procedure consists of a single INSERT, UPDATE, DELETE or MERGE statement, the return value parameter is populated regardless of the setting of NOCOUNT. However, if your stored proc consists of two or more statements that include at least one INSERT, UPDATE, DELETE or MERGE statement, the return value parameter is NOT populated – unless you SET NOCOUNT ON.
SET NOCOUNT ON suppresses the DONE_IN_PROC message from being returned by the server to the client. From ADO’s perspective, it sees the stored procedure as being just one (or more) SELECT statement(s).
Poke Open Recordsets
When an open recordset is returned, for some reason ADO doesn’t like to give up the return value until you’ve given the recordset a nudge. This is as simple as testing the Recordset.EOF property is true, eg.:
If rst.State = 1 Then
If rst.EOF Then
'do nothing - we just needed to invoke .EOF
Once this is done, you can examine the Value of the first parameter in the Parameters collection (the stored procedure’s return value).
Once you’ve got the return value, you’ll need to do something with it. The default value returned by a stored procedure is 0, so if the return value is non-zero then this failure needs to be handled in VBA. Errors should be handled in the application in which they occur. For SQL Server this can be done with a TRY..CATCH block. For example:
drop procedure [dbo].[spTestProc]
create procedure [dbo].[spTestProc]
set nocount on;
select [MyColumn] = 11 into #t1;
select [MyColumn] = 22/0 into #t2;
SQL Server will attempt the try block and fail (22/0 giving a divide by zero error). Control then passes to the CATCH block where a return value of 2 has been specified. If we change the code so that there is no division by zero, the specified return value of 1 is returned to our VBA Return Value parameter. You can use any numbers you want (but keep them below 32k – the maximum value for the Integer data type in VBA), but it is best practice to return 0 on success (I used 1 purely to distinguish from SQL Server’s default value). You may choose not to specify any, ie. remove the “return X” statements altogether. This would leave an empty CATCH block which is fine. The purpose of the CATCH block is to provide a container for code that is executed only when an exception is thrown. It’s up to you how you use it. You may want to use it to write the exception to an log table or PRINT the exception to the console. You can use it throw a custom exception (using RAISERROR) if you like. It’s entirely up to you.
TRY..CATCH supplements, but does not replace, your VBA Error Handling routine(s). Once you have your TRY..CATCH code in place, it’s important that your VBA code branches to your VBA Error Handler if the return value indicates failure.
In this blog, I have only scratched the surface of the ADODB.Command object. I have not talked about how to handle multiple recordsets that are returned by a stored procedure containing one or more SELECT statements. I have also not talked about the Errors collection of the ActiveConnection property of the ADODB.Command object which, in particular, can be used to capture the error description provided by SQL Server. The ADODB.Command object also has many properties that can be read to provide useful information about the environment in which the code is running. Hopefully, this blog has demonstrated to you that the ADODB.Command object is a very useful tool to have in your VBA Development toolbox.