Introduction

In the more recent releases of Microsoft Access, great effort has gone into making this product a full-featured relational database system. Stored procedures, a functionality usually associated with enterprise database systems such as SQL Server, can now be found in Access. Stored procedures in Access have been available since Access 2000 and are native to the Jet 4 Database Engine. If you're accustomed to using stored procedures in SQL Server, then you'll be right at home with how they're used in Access. However there are some limitations to keep in mind. I'll discuss those later on.

This article will be broken down into two parts. Part one will describe in detail how to create stored procedures in Access using ADO.NET and Visual Basic.NET. Part two will demonstrate how to utilize the stored procedures created in part one by assembling a data access tier that can be modelled and used in your own applications. The code in this article has been tested using Access 2002, although it should also work with Access 2000.

How do stored procedures work in Access?

Unlike other objects in Access, stored procedures have no interface and cannot be created or run through the Access User Interface. The way to get them into your database is to simply code them. I'll show how that's done in ADO.NET.

When a stored procedure is added to an Access Database, the Jet Engine reworks the stored procedure syntax into a query object. To an Access developer this may sound like unnecessary work to code a query. However, it does have its advantages. Consider an application that has to break out into different versions when maintaining both an Access Database and a SQL Server Database. Using stored procedures will make it easier to write the code for the database tier of the application as the program will change very little between the different versions.

Creating Stored Procedures

To demonstrate, I'll first show how to create the SQL statements to create stored procedures. At the end of the article I'll show the entire code needed to run these statements against the database. Using the Northwind database that comes with Access, four stored procedures will be created. Focusing on the Products table for all of them, let's start off with the easiest one; select all data of each row in the table. To create the stored procedure, execute the following SQL statement against the database:

"CREATE PROC procProductsList AS SELECT * FROM Products;"

The statement: "CREATE PROC procCustomerList" is the part that actually creates the stored procedure. The part following "AS" can be any valid SQL Statement.

Often in a stored procedure you'll want to pass a value to be used in the query. Consider that you may want to delete a record based on a particular ProductID. The following stored procedure shows how to do just that:

"CREATE PROC procProductsDeleteItem(inProductsID LONG)" & _
"AS DELETE FROM Products WHERE ProductsID = inProductsID;"

On the first line, notice the parenthesis right after the CREATE PROC declaration. There is a parameter defined as a Long value. This is where you add the variable to delete the record in question.

The next two statements show how to create an add and an update stored procedure for the Products table respectively. Note that not all fields are included for the sake of brevity:

"CREATE PROC procProductsAddItem(inProductName VARCHAR(40), " & _
"inSupplierID LONG, inCategoryID LONG) " & _
"AS INSERT INTO Products (ProductName, SupplierID, CategoryID) " & _
"Values (inProductName, inSupplierID, inCategoryID);"

"CREATE PROC procProductsUpdateItem(inProductID LONG, " & _
"                                   inProductName VARCHAR(40)) " & _
"AS UPDATE Products SET ProductName = inProductName " & _
"    WHERE ProductID = inProductID;"

Notice that a comma separates each parameter when more than one is specified.

Limitations

There are some limitations you may encounter here, especially if you're used to the power of SQL Server.

• Output parameters cannot be used.
• Don't use the @ character. The @ character is often used in Transact SQL (SQL Server), where it represents a local variable. Access doesn't always convert this character and will sometimes leave it out. This can cause esoteric bugs which can lead to premature hair loss.
• Temporary tables are not available in Access.
• I suspect many of the options available in Transact SQL are not available in Access as it's not Transact SQL compatible.

Conclusion

Hopefully, this article has provided some guidance in a nearly undocumented area of Access and Jet not yet explored by most. For more information on how the ADO.NET code works in the CreateStoredProc subroutine.The following is a complete listing of all code presented in this article:

Imports System
Imports System.Data
Imports System.Data.OleDb
Module CreateSP
    Sub Main()
        ProductsProcs()
    End Sub
    ' Products Stored Procs to be added to the db.
    Sub ProductsProcs()
        Dim sSQL As String
        ' procProductsList - Retrieves entire table
        sSQL = "CREATE PROC procProductsList AS SELECT * FROM Products;"
        CreateStoredProc(sSQL)
        ' procProductsDeleteItem - Returns the details (one record) from the 
        ' JobTitle table
        sSQL = "CREATE PROC procProductsDeleteItem(@ProductID LONG) AS " _
            & "DELETE FROM Products WHERE ProductID = @ProductID;"
        CreateStoredProc(sSQL)
        ' procProductsAddItem - Add one record to the JobTitle table
        sSQL = "CREATE PROC procProductsAddItem(inProductName VARCHAR(40), " _
            & "inSupplierID LONG, inCategoryID LONG) AS INSERT INTO " _
            & "Products (ProductName, SupplierID, CategoryID) Values " _
            & "(inProductName, inSupplierID,   CategoryID);"
        CreateStoredProc(sSQL)
        ' procProductsUpdateItem - Update one record on the JobTitle table
        sSQL = "CREATE PROC procProductsUpdateItem(inProductID LONG, " _
            & "inProductName VARCHAR(40)) AS UPDATE Products SET " _
            & "ProductName = inProductName WHERE ProductID = inProductID;"
        CreateStoredProc(sSQL)

    End Sub
    ' Execute the creation of Stored Procedures
    Sub CreateStoredProc(ByVal sSQL As String)
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand = New OleDbCommand()
        Dim da As OleDbDataAdapter
        ' Change Data Source to the location of Northwind.mdb on your local 
        ' system.
        Dim sConStr As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data " _
            & "Source=C:\Program Files\Microsoft " _
            & "Office\Office10\Samples\Northwind.mdb"
        con = New OleDbConnection(sConStr)
        cmd.Connection = con
        cmd.CommandText = sSQL
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
    End Sub

End Module