In this article, Burton Roberts demonstrates some of the advantages of writing code for SQL Server stored procedures and functions instead of VBA functions and subs.

T-SQL, the language used in stored procedures and throughout SQL Server, is very similar to VBA and, therefore, not very difficult for Access programmers to learn. When you first start using stored procedures, you’re likely to use them the way you use Access queries—to perform action queries and return ordered and filtered recordsets. As you become experienced with T-SQL, you’ll find that there are many benefits to writing more code in SQL Server stored procedures and functions and less in VBA modules. These benefits include increased performance, scalability, reuse, and easier maintenance. In this article, I’ll demonstrate three techniques you can use in your Access Data Project to leverage the power of SQL Server stored procedures and functions to gain these benefits:

• Using multiple statements in stored procedures
• Using SQL Server user-defined functions instead of VBA functions
• Using multiple output parameters instead of recordsets

For this article, I’ve contrived a small one-form project for managing the weekly schedules of students in a pre-school. The project contains the following objects:

• tblStudent: A table with a record for each student.
• tblFallSched: A table with five records for each student. Each record represents a day of the week and includes the student’s standard schedule for that weekday.
• procFallSchedUpdate: A stored procedure that updates the weekly schedule of one student and returns the weekly tuition rate.
• procLoadSchedule: A stored procedure that returns the weekly schedule of one student and the tuition rate.
• fn_WeeklyRate: A user-defined function that calculates the weekly tuition rate for one student.
• frmFallSchedule: A form that shows the schedule of an individual student and the calculated rate.

Use multiple statements in stored procedures

To change a student’s schedule, I use the check boxes on frmFallSchedule in the sample database. If any check box is clicked, an Update button becomes visible. Because the database is fully normalized, the form encapsulates information from six different records: one for the student header information and five for the schedule information. To save the changes, I click on the Update button. In an Access/Jet scenario, the application would probably loop through the controls calling an update query five times, each time with a different set of parameters. I can do it this way in an ADP as well, but that would involve five round-trips to the server. Since one stored procedure can carry out multiple actions, I can send all of the parameters to the server in one call and let the stored procedure update the table five times. This reduces the network traffic to the server—in this case, by 80 percent—improving performance and scalability.

The following is the stored procedure that updates the fall schedule. Notice the five UPDATE statements and the declared variable. In stored procedures, you can declare and use variables just like in a VBA procedure.

CREATE PROCEDURE procFallSchedUpdate

@intStuID smallint,

@intMon tinyint,

@intTue tinyint,

@intWed tinyint,

@intThu tinyint,

@intFri tinyint,

@curWeeklyRate float OUTPUT



As

DECLARE  @intScheduleSum tinyint



UPDATE tblFallSchedule

SET intDayPart = @intMon 

WHERE intStuID = @intStuID and intDayOfWeek = 1



UPDATE tblFallSchedule

SET intDayPart = @intTue 

WHERE intStuID = @intStuID and intDayOfWeek = 2



UPDATE tblFallSchedule

SET intDayPart = @intWed 

WHERE intStuID = @intStuID and intDayOfWeek = 3



UPDATE tblFallSchedule

SET intDayPart = @intThu 

WHERE intStuID = @intStuID and intDayOfWeek = 4



UPDATE tblFallSchedule

SET intDayPart = @intFri

WHERE intStuID = @intStuID and intDayOfWeek = 5



Set @intScheduleSum = @intMon + @intTue + @intWed

  + @intThu + @intFri



SET @curWeeklyRate =

  dbo.fn_WeeklyRate(@intScheduleSum)



RETURN

Use SQL Server functions instead of VBA functions

In the last line of the stored procedure, there’s a call to a user-defined function. User-defined functions are new in SQL Server 2000, which means that you can only load this project on your system if you have either SQL Server 2000 or MSDE 2.0 with the Service Release 1A patch for Office 2000 Professional. User-defined functions are more commonly used for calculating columns in SELECT queries, as in:

SELECT field1,

 dbo.fn_MyFunction(Param1, Param2…)

From tblMyTable

In the example, I used a SQL Server function as you’d typically use a VBA function to return a single value. The returned value is assigned to the output parameter @curWeeklyRate and returned to the form in a text box. Notice that when invoking a SQL Server function, I must fully qualify it using the owner identifier that, in this case, is the default “dbo” for the database owner.

I could have easily created a VBA function in a standard module to accomplish the same task, and, since it wouldn’t require making a call to the server, it wouldn’t diminish performance or scalability. If this were an Access/Jet application, I could even reuse the VBA function to calculate the debit column in a SQL INSERT query at billing time. This is Access/SQL Server, however, and I’m not able to use a VBA function in a SQL Server query. I need to use a SQL Server user-defined function instead. The side benefit to using a user-defined function stored on the server is that if I want to make a change in the logic of that calculation, I won’t have to update the Access Data Project front end on every workstation. I just change the function once on the server.

The following is the user-defined function that’s used in this sample project (from the accompanying Download file). Pay no attention to the nonsense logic. The logic could be anything. A VBA function that performs the same calculation follows for comparison:

CREATE FUNCTION fn_WeeklyRate

  (@intScheduleSum tinyint)

RETURNS float AS 

BEGIN

Declare @curWeeklyRate float



SET  @curWeeklyRate = 0

IF @intScheduleSum > 0

  BEGIN

  SET @curWeeklyRate = 50

  END

IF @intScheduleSum > 9

  BEGIN

  SET @curWeeklyRate = 100

  END

RETURN @curWeeklyRate

END

Here's the same function in VB:

Public Function WeeklyRate _

  (intScheduleSum as Integer) as Currency

  WeeklyRate = 0

  If intScheduleSum > 0 Then

      WeeklyRate = 50

  End If

  If intScheduleSum > 9 Then

      WeeklyRate = 100

  End If

End Function

Use multiple output parameters instead of a recordset

The form in the sample project exposes information from six different records: one master record from tblStudent and five detail records from tblFallSchedule. Rather than set up the form in the implied master/detail configuration with a main form bound to the student table and a subform bound to the schedule table, I chose to set up an unbound form. In an unbound form, there’s no form recordset, and the controls are all unbound. The fact that I know that there will always be five detail records on the form, one for each day of the week, means that I don’t have to use a bound grid. This allows me to avoid using resource-intensive ADO recordsets and, instead, leverage the ability of the SQL Server stored procedure to return multiple output parameters through an ADO command object. Avoiding ADO recordsets and using unbound forms can sometimes increase the performance and scalability of an application.

Alter Procedure procLoadSchedule

@intStuID smallint,

@curWeeklyRate float OUTPUT,

@intMon tinyint OUTPUT,

@intTue tinyint OUTPUT,

@intWed tinyint OUTPUT,

@intThu tinyint OUTPUT,

@intFri tinyint OUTPUT



As

DECLARE @intScheduleSum tinyint



SELECT @intMon = intDayPart FROM tblFallSchedule

  WHERE intDayOfWeek = 1 and intStuID = @intStuID

SELECT @intTue = intDayPart FROM tblFallSchedule

  WHERE intDayOfWeek = 2 and intStuID = @intStuID

SELECT @intWed = intDayPart FROM tblFallSchedule

  WHERE intDayOfWeek = 3 and intStuID = @intStuID

SELECT @intThu = intDayPart FROM tblFallSchedule

  WHERE intDayOfWeek = 4 and intStuID = @intStuID

SELECT @intFri = intDayPart FROM tblFallSchedule

  WHERE intDayOfWeek = 5 and intStuID = @intStuID



SET @intScheduleSum = @intMon + @intTue + @intWed

  + @intThu + @intFri

SET @curWeeklyRate =

  dbo.fn_WeeklyRate(@intScheduleSum)

RETURN

The following code from behind the form invokes the procLoadSchedule stored procedure and populates the controls on the form:

Private Sub LoadSchedule(intStuID As Integer)

Dim cnn As ADODB.Connection

Dim cmd As ADODB.Command

Dim prmStuID As ADODB.Parameter

Dim prmRate As ADODB.Parameter

Dim prmMon As ADODB.Parameter

Dim prmTue As ADODB.Parameter

Dim prmWed As ADODB.Parameter

Dim prmThu As ADODB.Parameter

Dim prmFri As ADODB.Parameter



Set cnn = CurrentProject.Connection

Set cmd = New ADODB.Command



'Set up the command object and execute

With cmd

  .ActiveConnection = cnn

  .CommandText = "procLoadSchedule"

  .CommandType = adCmdStoredProc

  Set prmStuID = .CreateParameter("@intStuID", _

      adInteger, adParamInput, , intStuID)

  .Parameters.Append prmStuID

  Set prmRate = .CreateParameter("@curRate", _

      adDouble, adParamOutput)

  .Parameters.Append prmRate

  Set prmMon = .CreateParameter("@intMon", _

      adInteger, adParamOutput)

  .Parameters.Append prmMon

  Set prmTue = .CreateParameter("@intTue", _

      adInteger, adParamOutput)

  .Parameters.Append prmTue

  Set prmWed = .CreateParameter("@intWed", _

      adInteger, adParamOutput)

  .Parameters.Append prmWed

  Set prmThu = .CreateParameter("@intThu", _

      adInteger, adParamOutput)

  .Parameters.Append prmThu

  Set prmFri = .CreateParameter("@intFri", _

      adInteger, adParamOutput)

  .Parameters.Append prmFri

  .Execute

End With



'Fill the controls on the form

Me.txtWeeklyRate = prmRate.Value

Me.fraMon = prmMon.Value

Me.fraTue = prmTue.Value

Me.fraWed = prmWed.Value

Me.fraThu = prmThu.Value

Me.fraFri = prmFri.Value



Set prmStuID = Nothing

Set prmRate = Nothing

Set prmMon = Nothing

Set prmTue = Nothing

Set prmWed = Nothing

Set prmThu = Nothing

Set prmFri = Nothing



Set cmd = Nothing

Set cnn = Nothing



End Sub

Use a SQL Server cursor instead of looping through an ADO recordset

Every once in a while you need to loop through an ADO recordset and perform a separate procedure for each record. This can be very inefficient, and you should always try to find an alternative that uses a single action query. Sometimes, however, finding one action query to do the job is impossible, especially if each turn through the loop might conditionally trigger one of several different procedures.

When you find yourself in this situation, you can minimize network traffic and dramatically improve performance by using a SQL Server cursor in a stored procedure instead of using a VBA Do Loop on an ADO recordset. The syntax for using SQL Server cursors is quite complex, so I can’t cover it here. However, there are many good resources where you can learn about them, including the Help file that comes with SQL Server—Books Online.

In this article, I’ve shown how to leverage the programming features of SQL Server to improve the performance and scalability of an Access Data Project. If you haven’t worked with SQL Server or MSDE yet, this is a good time to start. The next generation of ADO, called ADO.NET, is optimized for SQL Server. You might want to use it even if you don’t have to.