A common subject on the various newsgroups is that of stored procedures in Access. Until tonight, I was under the impression that stored procedures could not be used in Access except through an Access Project or a pass-through query. I was right to an extent. Some Data Definition Language (DDL) commands (including CREATE PROCEDURE) are valid commands when executed against an Access database when connecting to the database via the Jet 4 OleDb provider.

Despite claims that stored procedures could be created and used directly by Access I had never seen a functional example of an Access stored procedure. To those that I have debated with regarding this, I concede that CREATE PROCEDURE works with Access; Access does indeed support an extremely limited type of stored procedures starting with Access 2000.

There is a great deal of documentation floating around the internet pertaining to this subject. Nearly all of the documentation states that Access does not support stored procedures. Of the documentation that claims that Access does support stored procedures, most of it provides no example of how to create and execute them.

When I saw yet another post on this subject tonight, I began typing the typical reply of “You can’t do it, Access doesn’t support it,” but also continued looking for information to back up my claim. Everything I found in previous searches on this subject fit into the categories I described above. Tonight I stumbled across a fairly recent newsgroup discussion and a Microsoft Knowledge Base article both dealing with this subject directly.

The CREATE PROCEDURE command in Access cannot be used directly from the IDE. It must be executed over a separate connection using the Jet 4.0 OleDb provider.

The examples provided in the links above are specific to the VB Modules in Access. I decided to carry this a bit further and try this from within the .NET Framework. Below is a simple Console application that I wrote to test this. The code isn’t anything complicated; in fact, it should be pretty simple for anyone familiar with the OleDb namespace classes.

using System;

using System.Data;

using System.Data.OleDb;

using System.Text.RegularExpressions;



namespace AccessSPTest

{

  public abstract class AccessTests

  {

    public static void Main()

    {

      using (OleDbConnection cn = new OleDbConnection(

        @"Provider=Microsoft.Jet.OLEDB.4.0;" +

         "Data Source=c:\inetpub\wwwroot\recipemanager\recipe.mdb"))

      {

        byte option = GetOption();

        while(option!=7)

        {

          switch(option)

          {

            case 1:

              CreateSelectProcedure(cn);

              break;

            case 2:

              DropSelectProcedure(cn);

              break;

            case 3:

              ExecuteSelectProcedure(cn);

              break;

            case 4:

              CreateInsertProcedure(cn);

              break;

            case 5:

              DropInsertProcedure(cn);

              break;

            case 6:

              ExecuteInsertProcedure(cn);

              break;

            case 7:

              break;

            default:

              Console.WriteLine("Invalid Option.\n");

              break;

          }



          option = GetOption();

        }

      }

    }



    private static byte GetOption()

    {

      Regex re = new Regex(@"^\d{1}$");

      byte option = 0;

      while(option==0)

      {

        Console.WriteLine("Select an option:");

        Console.WriteLine("\t1.) Create Select Procedure");

        Console.WriteLine("\t2.) Drop Select Procedure");

        Console.WriteLine("\t3.) Execute Select Procedure");

        Console.WriteLine("\t4.) Create Insert Procedure");

        Console.WriteLine("\t5.) Drop Select Procedure");

        Console.WriteLine("\t6.) Execute Insert Procedure");

        Console.WriteLine("\t7.) Exit");

        Console.Write("Enter a number (1-7): ");

        string input = Console.ReadLine();



        if(re.IsMatch(input))

          option = Convert.ToByte(input);

      }

      return option;

    }



    private static void DropSelectProcedure(OleDbConnection conn)

    {

      conn.Open();

      try

      {

        OleDbCommand cmdDrop = new OleDbCommand(

          "DROP PROCEDURE sp_Ingredient2",

          conn);

        cmdDrop.CommandType = CommandType.Text;

        cmdDrop.ExecuteNonQuery();

      }

      catch(Exception ex)

      {

        Console.WriteLine("Procedure could not be dropped.");

        Console.WriteLine(ex.Message);

      }

      finally

      {

        conn.Close();

        Console.WriteLine(String.Empty);

      }

    }



    private static void CreateSelectProcedure(OleDbConnection conn)

    {

      conn.Open();

      try

      {

        OleDbCommand cmdCreate = new OleDbCommand(

          "CREATE PROCEDURE sp_Ingredient2(prmIngredientID int)" +

          " AS SELECT * FROM Ingredient WHERE IngredientID = prmIngredientID",

          conn);

        cmdCreate.CommandType = CommandType.Text;

        cmdCreate.ExecuteNonQuery();

      }

      catch(Exception ex)

      {

        Console.WriteLine("Procedure could not be created.");

        Console.WriteLine(ex.Message);

      }

      finally

      {

        conn.Close();

        Console.WriteLine(String.Empty);

      }

    }



    private static void ExecuteSelectProcedure(OleDbConnection conn)

    {

      int id = 0;

      Regex re = new Regex(@"^\d+$");

      do

      {

        Console.Write("Ingredient ID: ");

        string input = Console.ReadLine();

        if(re.IsMatch(input))

          id = Convert.ToInt32(input);

      } while(id == 0);

			

      OleDbCommand cmd = new OleDbCommand("sp_Ingredient2", conn);

      cmd.CommandType = CommandType.StoredProcedure;

      cmd.Parameters.Add(new OleDbParameter("prmIngredientID", OleDbType.Integer));

      cmd.Parameters["prmIngredientID"].Value = id;

			

      conn.Open();

      try

      {

        using(OleDbDataReader reader = cmd.ExecuteReader())

        {

          while(reader.Read())

          {

            // Write the ID and Ingredient Name

            Console.WriteLine("{0}\t{1}", reader.GetInt32(0), reader.GetString(1));

          }

        }

      }

      catch(Exception ex)

      {

        Console.WriteLine("Could not execute select procedure.");

        Console.WriteLine(ex.Message);

      }

      finally

      {

        conn.Close();

        Console.WriteLine(String.Empty);

      }

    }



    private static void DropInsertProcedure(OleDbConnection conn)

    {

      conn.Open();

      try

      {

        OleDbCommand cmdDrop = new OleDbCommand(

          "DROP PROCEDURE sp_InsertIngredient", conn);

        cmdDrop.CommandType = CommandType.Text;

        cmdDrop.ExecuteNonQuery();

      }

      catch(Exception ex)

      {

        Console.WriteLine("Procedure could not be dropped.");

        Console.WriteLine(ex.Message);

      }

      finally

      {

        conn.Close();

        Console.WriteLine(String.Empty);

      }

    }



    private static void CreateInsertProcedure(OleDbConnection conn)

    {

      conn.Open();

      try

      {

        OleDbCommand cmdCreate = new OleDbCommand(

          "CREATE PROCEDURE sp_InsertIngredient(prmIngredientName varchar(25))" +

          " AS INSERT INTO Ingredient (Description) VALUES (prmIngredientName)"

          , conn);

        cmdCreate.CommandType = CommandType.Text;

        cmdCreate.ExecuteNonQuery();

      }

      catch(Exception ex)

      {

        Console.WriteLine("Procedure could not be created.");

        Console.WriteLine(ex.Message);

      }

      finally

      {

        conn.Close();

        Console.WriteLine(String.Empty);

      }

    }



    private static void ExecuteInsertProcedure(OleDbConnection conn)

    {

      string value = String.Empty;

      do

      {

        Console.Write("Value to insert: ");

        value = Console.ReadLine();

      } while(value == String.Empty);

		

      OleDbCommand cmd = new OleDbCommand("sp_InsertIngredient", conn);

      cmd.CommandType = CommandType.StoredProcedure;

      cmd.Parameters.Add(new OleDbParameter("prmIngredientName",

        OleDbType.VarChar, 25));

      cmd.Parameters["prmIngredientName"].Value = value;

       conn.Open();

      try

      {

        using(OleDbDataReader reader = cmd.ExecuteReader())

        {

          while(reader.Read())

          {

            // Write the ID and Ingredient Name

            Console.WriteLine("{0}\t{1}",

              reader.GetInt32(0), reader.GetString(1));

          }

        }

      }

      catch(Exception ex)

      {

        Console.WriteLine("Could not execute insert procedure.");

        Console.WriteLine(ex.Message);

      }

      finally

      {

        conn.Close();

        Console.WriteLine(String.Empty);

      }

    }

  }

}

Note that this code does not check if an object exists before the drop statement is called. Access keeps track of objects in a system table called MSysObjects. Checking if an object exists requires some up front configuration.

The first step towards verifying the presense of an object is to make sure that system tables are visible in Access. If they are not visible, go to Tools / Options… and check the box for system tables in the show group on the View tab. Apply the change.

The second step is to ensure that the user account has read access to the design and data of the MSysObjects table. By default this access has not been granted. To enable the access, go to Tools / Security / User and Group Permissions… Select the appropriate user account and ensure that “Object Type” is set to “Table” then click the MSysObjects table from the “Object Name” list. Click “Read Data,” “Read Design” should be checked automatically. Apply the change.

At this point, the permissions to access MSysObjects should be set properly. To check if an object exists, execute the following command:

SELECT COUNT(*) FROM MSysObjects WHERE Name=?

Executing the above statement through an OleDbCommand using ExecuteScalar() will return an integer of 0 or 1. If the value is 1, the object exists.

OleDbCommand cmd = new OleDbCommand(

  "SELECT COUNT(*) FROM MSysObjects WHERE Name=?", conn);

cmd.Parameters.Add(new OleDbParameter("param1", OleDbType.VarChar, 255));

cmd.Parameters["param1"].Value = object_name;

conn.Open();

int matches = (int)cmd.ExecuteScalar();

if(matches==1)

  Console.WriteLine("Object Found.");

else

  Console.WriteLine("Object Not Found.");

conn.Close();

The DDL seems to follow the SQL standards pretty well in terms of syntax but that is where the commonality stops. Recall that I said that these stored procedures are extremely limited. To see how limited these so called “stored procedures” truly are, a look under the hood is necessary.

Like most modern database management systems, Access database objects are divided into a number of categories including Tables, Users, etc… For the purposes of this discussion, I will limit the focus to Access’ Query type.

Whenever an object is created in Access, a row is added to the MSysObjects system table. The MSysObjects table (which we made visible earlier) maintains configuration and reference data about each object including its type. A value of “5” in the Type column indicates that the object represented by the given row, is a stored query.

Any object created with the CREATE PROCEDURE command has a type of 5. This tells us that a procedure was not created but a QueryDef was created instead. In fact, the online Office reference makes no attempt to hide that fact as is indicated in the following excerpt. I’ve provided the link for reference.

procedure

Similar to a view except that the SQL syntax can allow for SELECT statements (row returning queries) to pass parameters and allow for DML (non-row returning queries) UPDATE, INSERT, DELETE, and SELECT INTO. Literally the same as a querydef, except that it can be created from SQL syntax without the use of the DAO object model.

In Access 10 (I haven’t checked later versions), these objects are listed in the Query list and are displayed with the corresponding “Action Query” icon. We can also look at the SQL definition of one of the objects for additional confirmation.

PARAMETERS prmIngredientName Text ( 255 );

INSERT INTO Ingredient ( Description )

SELECT prmIngredientName AS Expr1;

What does all of this looking under the hood mean? It means that the objects that are created with the CREATE PROCEDURE command are not stored procedures as enterprise developers know them. They provide no support for any type of flow control statements or other logic; they are simply single statement queries that are capable of accepting parameters. This is nothing more than a roundabout way to create objects that are accessible from the Query menu.

This being said, it would be nice to be able to execute a statement such as:

IF EXISTS(SELECT * FROM MSysObjects WHERE Name="objName") DROP PROCEDURE objName

but statements like that just won’t work due to the limited functionality of Access’ SQL processor.

More often than not, Access is not the proper tool for the job. Don’t get met wrong, Access is great for small, workgroup based solutions. Solutions requiring more power (such as SQL Server or Oracle) are needed when the problem being addressed extends much further than that. Misleading statements such as “Access supports stored procedures” only serve to compound scalability and performance issues when inexperienced developers are faced with the decision of which DBMS to use.