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
@curWeeklyRate float OUTPUT
DECLARE @intScheduleSum tinyint
SET intDayPart = @intMon
WHERE intStuID = @intStuID and intDayOfWeek = 1
SET intDayPart = @intTue
WHERE intStuID = @intStuID and intDayOfWeek = 2
SET intDayPart = @intWed
WHERE intStuID = @intStuID and intDayOfWeek = 3
SET intDayPart = @intThu
WHERE intStuID = @intStuID and intDayOfWeek = 4
SET intDayPart = @intFri
WHERE intStuID = @intStuID and intDayOfWeek = 5
Set @intScheduleSum = @intMon + @intTue + @intWed
+ @intThu + @intFri
SET @curWeeklyRate =
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:
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
RETURNS float AS
Declare @curWeeklyRate float
SET @curWeeklyRate = 0
IF @intScheduleSum > 0
SET @curWeeklyRate = 50
IF @intScheduleSum > 9
SET @curWeeklyRate = 100
Here's the same function in VB:
Public Function WeeklyRate _
(intScheduleSum as Integer) as Currency
WeeklyRate = 0
If intScheduleSum > 0 Then
WeeklyRate = 50
If intScheduleSum > 9 Then
WeeklyRate = 100
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
@curWeeklyRate float OUTPUT,
@intMon tinyint OUTPUT,
@intTue tinyint OUTPUT,
@intWed tinyint OUTPUT,
@intThu tinyint OUTPUT,
@intFri tinyint OUTPUT
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 =
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
.ActiveConnection = cnn
.CommandText = "procLoadSchedule"
.CommandType = adCmdStoredProc
Set prmStuID = .CreateParameter("@intStuID", _
adInteger, adParamInput, , intStuID)
Set prmRate = .CreateParameter("@curRate", _
Set prmMon = .CreateParameter("@intMon", _
Set prmTue = .CreateParameter("@intTue", _
Set prmWed = .CreateParameter("@intWed", _
Set prmThu = .CreateParameter("@intThu", _
Set prmFri = .CreateParameter("@intFri", _
'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
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.
Your download file is called 104Roberts.zip in the file SA2001-04down.zip
This can be purchased with all the other downloads on this page