An ADO Command Factory for Stored Procedures

Burton Roberts

There are some technical and bureaucratic obstacles to using ADO command objects. Since using stored procedures can make a tremendous difference to your Access application, Burton Roberts supplies a simple way to get around the first problem.

Recently, I read an article on writing "Efficient VB Code" where the authors wrote strings of SQL code in VBA modules and opened ADO Recordsets using those strings. Interesting, but certainly not efficient. Typically, the code looked something like this:

Dim rst as ADODB.Recordset
Dim strSQL as String

strSQL =  "SELECT fld1, fld2, FROM tbl1,..."
Set rst = New ADODB.Recordset
Rst.open strSQL, mstrConnect

While this code works, it doesn't take advantage of the precompiled objects in the database, like SQL Server or MSDE stored procedures, or Access queries. Using stored procedures instead of sending SQL strings in VBA is faster and uses fewer resources than submitting raw SQL commands from your code.

As I read the article, I tried to understand why a programmer might want to avoid using stored procedures in favor of SQL strings. One reason is that using a stored procedure can be a pain in the neck. After all, you first have to create the stored procedure on the server. On the front end or middle tier, you have to wrap that stored procedure in an ADO command object, which might entail creating and appending ADO Parameter objects. In addition, you might not have the authority to create new objects on the server, requiring you to go through the DBA, wait three weeks, and sacrifice a goat to get a stored procedure created. If all you do is write some simple queries and get some records back, it's no wonder some programmers skip all of that and open Recordsets directly with SQL strings. They're trying to be "efficient"— it's just a different kind of "efficiency" than speeding up a program. In this case, it means getting your job done on time and within a budget. If the code is a little slow or not so scalable, maybe the people who write the paychecks won't notice.

The Command Factory function

I can't do anything about your bureaucratic problems, but what if you could use a flexible, reusable function that returned an ADO command object for all of your stored procedures? You could have simple, truly efficient code like this:

Dim rst as ADODB.Recordset
Set rst = New ADODB.Recordset
Rst.Open cmdStoredProc(strStoredProcName, _
  varParam1, lngDatatype1, varParam2, ...)

Or, if your stored procedure performed an action query, the code would simply be:

cmdStoredProc(strStoredProcName, varParam1, _
  lngDatatype1, varParam2, lngDatatype2,...).Execute

You'll still have to write SQL for the stored procedure on the server (and get the DBA to set it up), but now you don't have to write SQL strings in the VBA module. Using this new function, you don't have to dimension or instantiate the command object in the calling procedure, nor do you have to create and append parameters—it's all done in the function procedure. The only problem with this technique is that you have to remember how ADO data types map to SQL Server data types without the benefit of IntelliSense. The old-fashioned way still works—just keep a list next to your computer (I've included that list in Table 1).

Table 1. SQL Server and Access data types.

The function begins by finding the size of ParamArray avar that is the sole parameter to the function:

Public Function cmdStoredProc(ParamArray avar() _
       As Variant) As ADODB.Command
Dim prm As ADODB.Parameter
Dim lngElements As Long
Dim i As Long

On Error GoTo HandleErr
i = 0

The key to this function is using ParamArray as the parameter. It assumes that the first element in the array, avar(0), is the name of the stored procedure. I use that to set up a Command object. Since this sample code is to be used in an Access Data Project, I use the CurrentProject. Connection as my active connection. You might have to change this in other environments to a connection string. As I'll discuss later, the total number of parameters should be an even number, so I check for that before pulling elements out of the array:

lngElements = UBound(avar())
If lngElements > 1 Then  
If Not (lngElements Mod 2) Then
  Set cmdStoredProc = New ADODB.Command
  With cmdStoredProc
     .ActiveConnection = CurrentProject.Connection 
     .CommandText = CStr(avar(0))
     .CommandType = adCmdStoredProc

The following elements represent the stored procedure parameters and their data types. After the function reads the stored procedure name into the CommandText property, it loops through the remaining elements of the array two at a time. The odd indexed elements in the array are the stored procedure parameter values, and the even indexed elements are the data types for those parameters (that is, the first parameter of the stored procedure is in the array element avar(1), while its data type is in avar(2); the second value is in avar(3), with its data type in avar(4), and so on for as many parameters as the stored procedure needs):

      For i = 0 To lngElements / 2 - 1
        Set prm = .CreateParameter("Param" & _
         CStr(i + 1), _
          CInt(avar(2 * i + 2)), adParamInput, _
          Len(avar(2 * i + 1)), avar(2 * i + 1))
        .Parameters.Append prm
      Next i
     End If

If there are three parameters, then there will be seven array elements, the last one being avar(6) because the array is zero-based. The variable lngElements evaluates to six, and the loop runs three times from i = 0 to i = 2 (6 / 2 – 1 = 2). So, when i = 2, 2 * i + 1 = 5 represents the array index of the third parameter, and 2 * i + 2 = 6 represents the array index of the third parameter's data type.

Handling output

What about output parameters? Only two changes are needed to accommodate one output parameter, which is usually the case.

The first change is that you must pass in one more element in the array—the data type of the output parameter—so that now you're checking for an odd number of array elements. The second change is that this version of the function doesn't return a Command object, but instead runs the stored procedure and returns a variant containing the value of the output parameter. The code to add the output parameter, execute the Command object, and return the result in the output parameter looks like this:

Set prm = .CreateParameter("ParamOutPut", _
        CInt(avar(lngElements)), adParamOutput, 255)
       .Parameters.Append prm
       .Execute
       varStoredProc = prm.Value  

In the sample project for for my article, "Temporary Tables in Access Data Projects" (also in this issue), I had a function procedure, GetSessionID(), that called a stored procedure and returned an output value, and I created an ADO Recordset from a command object in the sub GetRecords. I could have used varStoredProc() and cmdStoredProc() instead and saved myself from writing so much code. The entire code behind the form would have looked like this:

Private Sub cboStudentID_AfterUpdate()
  Dim rst As ADODB.Recordset
  Set rst = New ADODB.Recordset

  'Fill the report table and return the new session ID to   
  'the form
  Me.txtSessionID = varStoredProc("procStudentMajorTT", _
    Me.txtStudentID, adVarChar, adInteger)

  'Get the recordset from the report table filtered by   
  'the session ID
  Rst.open cmdStoredProc("procStudentMajorReport", _
    Me.txtSessionID, adInteger)

  Set Me.Recordset = rst
  Set rst = Nothing
End Sub

Using these functions is simple, and they make your code leaner and easier to follow. You receive this convenience along with the benefits of using those fast, precompiled stored procedures.

To find out more about Smart Access and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57

Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.

This article is reproduced from the September 2000 issue of Smart Access. Copyright 2000, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Smart Access is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-493-4867 x4209.