Improving on VBA with Transact-SQL

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.

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:

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.

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

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

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.