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.