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.
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
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
A great post about a great subject. This is helpful for anyone who wants to use SQL Azure which does not support ADODB. Thanks Juan!
Patrick Wood
Gaining Access
Thanks Pat! Looking forward to your review of the next part in this series.
Juan
Quick question. I am currently looking into following scanario.
1. Execute a MS SQL stored procedure from Access 2010.
2. Once the MS SQL stored procedure executes it should update the data in the Access table.
Please let me know if step 2 is possible.
Thank you
Syed
Syed,
If the data is in SQL Server than the stored procedure should automatically take care of it. If the data is stored in Access, you can create a pass through query that executes the stored procedure and return your data, than use the results in a update query in Access to update your local data.
Hope that helps,
Juan
Yes the data is going to be in Access database. If possible can you please provide me some example of this?
Thank you in advance.
Syed,
Just follow the instructions in this post to create a pass through query, than just do a normal Access query to update your table using both the table and the pass through created before.
Juan
thank you.
With SSMA for Access you can upload your Access queries to the Views folder in Sql server 2008. How can I make such a query the new data source of my Access form?
Sql Beginner
To use your view as a form source you can use several approaches. The easiest way would be to link the view using a DSN. Access will treat the view as if it was another linked table.
If you plan on editing, adding or deleting records with your form you are going to run into trouble. Access will need you to define a primary key on the view in order to edit the data. I wrote a post on the topic here:
http://accessexperts.net/blog/2010/03/03/using-sql-server-views-with-access-index-needed-for-editing-data-2/
If your view has multiple tables in it and you try to delete a record it may cause an issue with SQL Server, since it may not know which record to delete from what table. In that instance you will need to create a delete trigger on your SQL View.
Hope that helps,
Juan
Hi — Might I be able to loop through a group of sprocs as a collection from within Access? My true question is: how to pull in the list of names of sprocs from a SqlSrvr database to manipulate with VBA?
Thanks —
jwk
Great question jwk. Yes, anything you can do in SQL Server Management Studio, that returns records, you can do in a query a pass through query, and that includes a list of tables, stored procedures, users,etc. Just look for the right syntax on the web and place it in a pass through query.
Good Luck!
Juan
I have connected an Access database to MS SQL Server Magement Studio through Linked Server. Everything works fine. I have a question. I have created a stored procedure through management studio and it works fine. but if I open the access database with MS Access I cannot see the stored procedure. Do you know where is stored?
Hi Kervin!
Unless you are using a Microsoft Access project file you will not be able to “see” the stored procedure. In order to use stored procedures in Access you will have to either use a pass-through query or ADODB command object in your code. I have examples of both on this post and in part 2.
Happy New Year!
Quick correction to part 2 of your post:
Under the heading “ODBC Connect Str Property” you have a list of bullets, the second of which is:
•Type the following: ?CurrentDb.TablesDefs(“TableName“).Connect
There is an erroneous “s” after “Table”. The bullet should read:
•Type the following: ?CurrentDb.TableDefs(“TableName“).Connect
Thanks Phil! Will correct.
Sorry, Part 1, not Part 2. That was erroneous.