Somos Expertos Access. Contacto atravez de Skype Skype logo expertos_7

SQL Server Stored Procedure Guide for Microsoft Access – Part 1

This is Part 1 in a series on Stored Procedures. 
View Part 2
View Part 3 

Stored Procedures are the lifeblood of a great SQL Server database, followed closely by Views. If you’re not tapping into their potential with your Access application then you’re not taking advantage of all SQL Server has to offer Access. In this first part of a series of posts, I’m going to discuss techniques you can use to incorporate stored procedures in your Access code.

Click for a free quote.

In order to use Stored Procedures you need to learn how to create them
If you haven’t done so, go ahead and learn how to create SPs and then come back here to continue reading.  As an Access developer, I was delighted to discover how easy and powerful the T-SQL language can be, any Access developer worth their salt will not have a problem picking up the language and diving right in. I found the process very gratifying and a natural upgrade path for me and I’m sure it will be for you as well. For more help with SQL Server please browse our SQL Server category on this blog.

To create SPs you need to use SQL Server Management Studio, (SSMS), which I found to be a joy to learn. If you don’t have SSMS then you’ll need to install SQL Server Express with tools from Microsoft’s website. I recommend installing SQL Server Express R2.

Test your Stored Procedure BEFORE using it in Access
You MUST make sure your SP is working correctly BEFORE trying to use it in Access. You don’t want to spend countless hours troubleshooting  a problem only to realize it was in your SP code all along. A good way to tell is running your SP in SQL Server Management Studio, (SSMS), and verifying the results are correct before incorporating it into Access.

Two methods of using Stored Procedures in Access
The first and easiest method is to use a pass through query with the SQL code invoking the stored procedure. This type of query is ideal when you need to base a report or form on a stored procedure and will be the focus of this first post.

The second method is using a ADODB recordset to pass along parameters to your stored procedure and receiving data back for processing.  We will discuss this method in the second part of the series.

Pass Through Queries Primer
This type of query is not as common as the regular Access query since it’s not as intuitive and doesn’t support the Query By Example interface, you must use SQL View and type in SQL yourself. It’s used when you need to bypass Jet (now renamed ACE) engine altogether and send your SQL directly to a ODBC source such as SQL Server, Oracle and any other ODBC compliant RDBMS software.

To create a pass through query in Access start by clicking on the Create tab, Query Design, click on Cancel button and changing the query type to Pass Through.

ODBC Connect Str Property
Pass through queries use ODBC to fetch the data from the server so you need to setup the connection string in the query’s property window. Arguably, this is the most daunting task for casual users, there is no help on how to add the ODBC string to the query and many users may just quit before they even started. So I came up with a quick technique that uses an existing linked table’s ODBC connection info:

  • Launch the debug window by pressing Control-G
  • Type the following: ?CurrentDb.TableDefs(“TableName“).Connect
  • TableName is a linked table in your database that is using the same ODBC connection as the query your creating.
  • Copy the string returned into your query’s ODBC Connect Str property.

For help with SQL Server connection strings in Access please see my post here. Also visit ConnectionStrings.com.

Note: In VBA, the property is called “Connect”, despite the property sheet labeling it differently.

Query SQL Code – What can you do?

Once you have the ODBC string in the query you’re ready to enter the specific SQL code you wish to use, but what can you do with a pass through? The answer is: anything the server will understand, in the case of SQL Server:

  • Select Queries
  • Action Queries
  • T-SQL – Add users, modify security rights, etc
There really is no limit to what you can do, the same SQL in SSMS can be executed in a pass through if you have the proper permissions.
Creating Pass Through Queries in Code

Below is an example of how you would create a pass through in your code:

The procedure takes four arguments: strSQL would be the SQL code, strQueryName is the name of the query, strConnection would be the connection string and bolExecute is used to execute the query immediately or just save it for later use.

A couple of items of note regarding the code above: notice there is no qdf.Save, once you execute CreateQueryDef the system has created the query and saves your changes automatically. Second, if your query already exists the system will simply open the query instead of creating it.

Temporary Queries
You can create temporary queries in your code by leaving out the query name, for example:

When the procedure terminates you’re left with no query in the database, which is great for good housekeeping if the query is not intended for later use. For example, if you wish to create a temporary query with the code above you could invoke the procedure like this:

 

Re-linking Database – Make sure you take care of Pass Through Queries too
Please keep in mind your pass through queries when you re-link your tables to a new database. The Link Table Manager will take care of your tables but not your pass through queries, you will need to manually update them or create code to take care of it.

ADODB and Stored Procedures – The preferred Way
In my next post I’ll discuss my preferred method of using stored procedures in my code.

This is Part 1 in a series on Stored Procedures. 
View Part 2
View Part 3 

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

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