Somos Expertos Access. Contacto atravez de Skype Skype logo expertos_7

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:

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:

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:

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 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:

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
Until then,
Juan
This is part 2 in a multi-part series on Stored Procedures with Access, Part 1 can be found here and Part 3 here.
Acerca de

Juan Soto es un desarrollador senior de Access en IT Impact Inc. y un MVP de Microsoft Access y se especializa en Access con bases de datos de SQL Server. Su pasión por el acceso lo ha llevado ayudar a una amplia gama de empresas a establecer un entorno seguro, estable y eficiente con SQL Server. Él es un orador frecuente en los grupos de usuarios de Access en todo el país y recientemente habló en el Orange County SQL Saturday # 73. Si desea que Juan hable en su próxima reunión de grupo, puede ponerse en contacto con él aquí.

Publicado en Access Help, SQL Server, VBA

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

*

 

Quienes Somos

ExpertosMicrosoftAccess.com es un servicio de la empresa IT Impact, Inc., una compañía de programación y servicios para empresas en Latino América. Ofrecemos servicios en .Net, SQL Server y Microsoft Access. Muchos de nuestros desarrolladores han obtenido el galardón de Access MVP, un título proveído por Microsoft a aquellos que han hecho aportes a la comunidad y que han demostrado tener conocimientos superiores del producto.

Nuestro Equipo

  • Le ayudamos a "Descubrir el poder de sus datos™" con reportes y sistemas de Access excepcionales .
  • Creamos soluciones de bases de datos personalizadas utilizando Microsoft Access y / o SQL Server.
  • Nuestros consultores ganaron sus estrellas en las empresas de servicios y/o manufactura antes de convertirse en programadores.

Blogs anteriores