An ADO Command Factory for Stored Procedures

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  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.

SQL Server 7 data type ADO data type Enum value
binary adVarBinary
bit adBoolean
char adChar
datetime adDate
decimal adNumeric
float adDouble
int adInteger
money adCurrency
nchar adWChar
ntext adLongVarChar
numeric adNumeric
nvarchar adVarWChar
real adSingle
smalldatetime adDate
smallint adSmallInt
text adLongVarChar
timestamp adBinary
tinyint adUnsignedTinyInt
UniqueIdentifier adGUID
varBinary adVarBinary
varChar adVarChar

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.

Your download file is called  009ROBERTS.zip

Other Articles You May Want To Read At This Site

Comparing DAO and ADO Recordsets

Access Subquery Techniques

 

About Burton Roberts

I have been absent from the Access community for a few years as I plunged into .Net when it came out in 2001 (or was it 2002?). I recently learned WPF, WCF, Silverlight, and even started programming in C# just this past year. (When I saw Ken Getz at a conference late last year and told him I had finally switched to C# he called me a "traitor"!) So, I haven't done much new programming in Access except that my main business (school-aged child care) still runs on the Access program I wrote in the 90’s. I still find myself writing new features, queries and reports in it as my business demands. I keep telling my partner I am going to rewrite it in .NET with SQL Server, but I never get around to it, because no one cares. This thing just works, and I can’t justify the hundreds of hours it would take to redo it.
This entry was posted in Old Material. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.