Go to Top

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.

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

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.

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

14 Responses to "Stored Procedure Guide for Microsoft Access – Part 2"

  • Tom Vincent
    September 29, 2014 - 11:26 pm Reply

    good stuff, but part 3 is missing.

  • Why you should always use a recordset when executing stored proceduresaccessexperts.com | accessexperts.com
    April 1, 2013 - 9:50 am Reply

    […] talked about using a recordset with stored procedures before, but this time I wanted to emphasize the importance of always using a recordset when executing a […]

  • Brian
    August 9, 2012 - 3:03 am Reply

    Hey guys, awesome website! I’m very new to programming and I really enjoyed reading your blogs so far, so much so that I just subscribed!

    However, I’m hoping that you could help me out. I’m currently working on a project where I’m creating a database in SQL Server 2008 (SS) and then using Access 2007 as the front-end UI. I’ve linked the SS tables to Access using ODBC and had started to create a pass-through query before stumbling upon your website. Now I think ADODB may be the better method for my project.

    Where I’m running into some issues is in creating the StoredProc. I believe I’ve got it all complete and correct with the exception of setting my variables. My goal is to enable users to select certain records from a series of sequentially dependent combo boxes in the header of an Access form. Then I will have a button in the header that says “GO” and upon clicking that button, all selected records in the combo boxes will be sent back to SS as the parameters for my StoredProc. So as placeholders, what do I put as the values of my parameter variables in the StoredProc?

    Also, I’m not sure if I’ve provided enough information for you to answer this, but do you know if your “MyRecordSet” module would work for me in this situation?

    If you need any information I haven’t included please don’t hesitate to ask. I’ve refrained from including too much so that you won’t have to read a novel just to help me out.

    Any advise or assistance would be immensely appreciated!

    • Juan Soto
      August 9, 2012 - 3:13 am Reply

      Hi Brian!

      Glad to hear you love the site!

      In regards to the form: we use pass through queries all the time too so it’s not a bad thing…do you have the query bound to the form? If so keep in mind the data will be read only. If you instead need them to edit the data you will need to create a view and use that as a recordsource.

      EasyADODB is the way to go for sending commands and receiving data back from SQL Server. Here is the link to the post:
      http://accessexperts.net/blog/2011/01/21/easy-adodb-recordsets-and-commands-in-access/

      In regards to sending the arguments back to your stored procedure: use a text string! For example, if your usp has two arguments, one being numeric and the second is text and it’s called usp_MySPROC then you would use:
      strSQL = “Exec usp_MySPROC ” & MyNumericFieldFromTheForm & “,'” & MyTextFieldFromTheForm & “‘”
      ExecuteMyCommand strSQL

      Hope that helps!

      • Brian
        August 14, 2012 - 10:26 pm Reply

        Thank you so much Juan! I figured it all out thanks to your help. I’m just sorry it took me so long to get back to you and thank you. Truly a class act.

  • Srdjan Vasiljevic
    June 6, 2012 - 1:59 am Reply

    I am trying to use this approach to set stored procedure as a data source for my form, but I am getting the following error all the time

    The object you entered is not a valid recordset property

    The same stored procedure works just fine if used via pass through query.

    Here is my code:

    Dim rs As ADODB.Recordset
    Dim strSQL As String
    strSQL = “EXEC KuDo_Stanje”
    OpenMyRecordset rs, strSQL

    Debug.Print rs.RecordCount

    Set rs = Nothing

    I try to get RecordCount and always get -1.

    What might be the problem ?

    • Ben Clothier
      June 6, 2012 - 2:10 am Reply

      Binding an ADO recordset to a form has additional requirements that needs to be satisfied.

      Generally speaking, only client-side static ADO recordset may be bound to a form. SQL Server additionally allows you to bind a server-side keyset ADO recordset. Other types may not be valid. Therefore, you should pass in the appropriate arguments into the OpenMyRecordset to ensure you’re creating a client-side static or a server-side keyset (but only if you’re using SQL Server) recordset. You can then set it to an Access form.

      Recordcount returns -1 whenever the count is not available. An example is when you’ve opened a forward-only recordset. There’s no way of knowing how many records there are, and the count of records you may have discovered once you arrive at the end of forward-only recordset may be no longer valid (e.g. someone may have had added/removed records while you were looping through the forward-only recordset). If you google on “Recordcount property ADO”, you’ll find more details on when it may return -1 and when it will give you an accurate count. Use BOF/EOF property instead to check if there are records or not.

      I hope that helps.

      • Srdjan Vasiljevic
        June 6, 2012 - 2:23 am Reply

        First I would like to thank you for taking time and effort to help as all out.

        I managed to get recordset on my form using this code

        Dim qry As QueryDef
        Dim rs As Recordset

        Set qry = CurrentDb.QueryDefs(“SQL_QueryData”)
        qry.SQL = “EXEC dbo.KuDo_Stanje”
        qry.Connect = CurrentDb.TableDefs(“Invent1”).Connect
        Set rs = qry.OpenRecordset
        rs.LockEdits = True

        Set Me.Recordset = rs
        qry.Close

        But using this approach I can not filter form.

        I get the error:

        The search key was found in any record

        This form is only displaying data (no edit or delete). I just need to show it and be able to filter it.

        What is the best solution to make this work ?

        • Ben Clothier
          June 6, 2012 - 4:07 am Reply

          First, I want to point out that you’re no longer using ADO but you’re now using DAO. You could have just as well as set your form’s recordsource to an Access passthrough query with the SQL you just used and not need to write any VBA code.

          Secondly, since you want to filter, wouldn’t it be better to use a view that can express the same thing that your stored procedure is currently expressing, or add a parameter to your stored parameter with the desired key you can then use to filter? That way, the filtering will occur server-side and be more efficient than if you had pulled the whole result from the stored procedure then try to use Access to filter it locally.

          I hope that helps.

  • PC
    March 17, 2012 - 2:38 pm Reply

    I am excited about your blog as you seem to provide a solution that will allow much easier integration of SQL Server with my MS Access world.

    Please check part 1 of your blog to see if something is missing. It ends abruptly in the middle of a sentence, actually the middle of a word.

    • Juan Soto
      March 17, 2012 - 9:36 pm Reply

      thanks for pointing it out! its fixed. glad you love the blog please consider subscribing
      juan

  • Thomas Thuene
    March 8, 2012 - 12:56 am Reply

    The solution suggested here appears very elegant, thank you for sharing. I have a problem, though, to get this working. I have a sproc that basically just counts the rows in a table. The code below calls the sproc from within SQL Server.

    I would like to use your OpenMyRecordset method, but don’t know how to write the strSQL for the output parameter. I know the connection works, because I can use the function you have for action queries. Could you enlighten me?

    kind Regards, Thomas

    USE [MYData]
    GO

    DECLARE @return_value int,
    @ReCnt int

    EXEC @return_value = [dbo].[MyFirstProcedure]
    @Location = N’Allston’,
    @Status = N’New’,
    @ReCnt = @ReCnt OUTPUT

    SELECT @ReCnt as N’@ReCnt’

    SELECT ‘Return Value’ = @return_value

    GO

    • Juan Soto
      March 8, 2012 - 1:07 am Reply

      Hi Thomas!

      Thanks for your kind comment. Here’s how to use an SPROC with recordset in Access:

      Dim rs As ADODB.Recordset
      Dim strSQL As String

      strSQL = “EXEC MyFirstProcedure”
      OpenMyRecordset rs, strSQL

      With rs
      strSQL = !Return_Value
      end with

      Change your SPROC to return a select statement and that would be read into your recordset in Access. Don’t use output parameters. For example, your stored procedure can end in:
      Select Count(CustomerID) As Return_Value from tblCustomers

      Hope that helps!
      Juan

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
  • This field is for validation purposes and should be left unchanged.