Stored Procedure Guide for Microsoft Access – Part 2

This is part 2 in a multi-part series on Stored Procedures with Access, Part 1 can be found here and Part 3 here.

In the first part of my guide I talked about using Pass Through queries as a convenient way to use Stored Procedures in Microsoft Access, in this second post I’m going to take it a step further and show you how I exploit the power of Stored Procedures in my code.

Stored Procedures and ADODB – the BEST combo
I LOVE SQL Server! It’s a robust platform that can take on the most complicated of tasks with ease, (compared to the Access Date Engine), and one of the ways to maximize the relationship is through ADODB recordsets and commands. I’ve touched on the topic before here, but today I’m going to take it further with some additional tips on using SPs in your Access code.

When to use ADODB Command and when to use a Recordset
Use a command when you don’t expect or need any information back from SQL Server as the result of the stored procedure. For example, when you perform action queries such as inserting new records. Use a recordset when you need to process results back from your SP, (assuming you build a result into your SP, more on that later).

Using Parameters in Access with Stored Procedures
Almost all stored procedures use parameters or input values to produce an output or action. For example, I have a stored procedure called usp_SalesByVendor and it requires three parameters, a VendorID and starting and ending dates. The output is a one-column, one-row recordset containing a single dollar figure called TotalSales. When I execute it in SQL Server Management Studio, (SSMS), it’s done this way:

 Exec usp_SalesByVendor 129, '1/1'11', '7/1/11'

Where 129 is the Vendor ID, and two dates are 1/1/11 and 7/1/11. The stored procedure will then return a single dollar value figure:

TotalSales
$12,342

There is a parameter collection you could use but I prefer using a string expression in Access instead.

Here’s how I do it in VBA:

Dim strSQL AS String
Dim rs AS ADODB.Recordset0
strSQL = "Exec usp_SalesByVendor " & Me.VendorID & "'" & Me.StartDate & "', '" & Me.EndDate & "'"
OpenMyRecordset rs, strSQL
Me.lblVendorTotal.Caption = "Total Sales this Year: " & rs!TotalSales

I use this code in a vendor form in my database. VendorID, StartDate and EndDate are fields on the form. lblVendorTotal is a label on the form’s footer. Notice how SQL Server treats dates as string inputs surrounded by single quotes. OpenMyRecordset is a custom procedure I use in my code, to learn more about it click here.

Dashboard Technique
I love dashboards and always try to convince my clients to include them in their application. A dashboard is a collection of unrelated data intended to summarize the state of the business during a given time frame.  As great as they are, many dashboard implementations run the risk of taking to long to process, since your running through a set of queries to get all of the stats needed. I love showing the dashboard on startup, (the running joke is “If the dashboard is all green that day you can go golfing”), so it’s critical that it doesn’t take to long.

In my app a dashboard is simply one large label formatted to look like a dashboard. Here’s a sample:

Each one of the figures above is a separate query,  9 in all, that would take Microsoft Access too much time to process in comparison with SQL Server. My colleague, Ben Clothier, will continue this series by demonstrating and going over the SP code, I’m going to focus on the Access side of it.

To create the dashboard above I use usp_QuickMetrics. The stored procedure returns the following recordset:

I then open a recordset using the SP usp_QuickMetrics and cycle through the cursor to build my dashboard:

OpenMyRecordset rs, "Exec usp_QuickMetrics"
With rs
Me.lblThisWeekNewOrders.Caption = !ThisWeek1 & "/" & !ThisWeek2
Me.lblThisMonthNewOrders.Caption = !ThisMonth1 & "/" & !ThisMonth2
Me.lblYTDNewOrders.Caption = Format(!YTD1, "0,000") & "/" & !YTD2
End With

As I go through the recordset I’m assigning values to each label’s caption property until all of the metrics have been completed. You could use textboxes instead of labels. I picked the later to avoid users from changing the data.

Before doing a query in Access ask yourself, can the server do it better?
As a programmer you need to leverage SQL’s awesome processing power versus Access’s convenience. In almost all cases the server will win and so will your program. If it’s a process with only SQL Server tables you will be hardpressed to perform the operation in Access.

The Series Continues!

We are not done talking about SPs, upcoming posts will discuss:

• CTE, table variables and temp tables, which one is best? by Ben Clothier
• Comparing Access SQL techniques VS SPs, by yours truly