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.

SQL Server Stored Procedure Guide for Microsoft Access - Part 1

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:
Private Sub CreatePassThrough(strSQL as string, _
strQueryName as String, _
strConnection as String, _
bolExecute as Boolean)
On Errror GoTo ErrorHandler
   Dim qdf As DAO.QueryDef

   Set qdf = CurrentDB.CreateQueryDef(strQueryName) 
   With qdf
      .SQL = strSQL
      .Connect =  strConnection
      If bolExecute then
        .Execute 
      End If 
   End With 
set qdf = Nothing 
Exit Sub

ErrorHandler:
If Err.Number = 3012 Then
'Query already exists, just open existing
Set qdf = CurrentDB.QueryDefs(strQueryName)
Resume Next
End If
'It's another error, display message
MsgBox Err.Description
End Sub
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:
Set qdf = CurrentDB.CreateQueryDef("")
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:
CreatePassThrough strSQL, "", strConnection
 

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