Working with Stored Procedures in an MS Access Project

Microsoft Access Project gives you access to data stored on SQL Servers via familiar OLEDB technology. With an MS Access Project you can work in the client/ server mode as easily as in the file server mode, a mode you would have handled with earlier (prior to Access 2000) versions. An MS Access Project can only be created to work with MS SQL Server.

With the Access Project construct, Microsoft has extended the playground of MS Access to the RDBMS arena. The data is all stored on the SQL Server (or MSDE, its junior version), but the Access application will have access to this data via the OLEDB connectivity. The reader will benefit from reading the earlier article on creating an MS Access Project by connecting to a SQL Server by linked tables and the article on stored procedures.

Test data used in the tutorial

A Microsoft Access Project will be created to obtain data used in this tutorial. First, create an MS Access Project and choose to create a new project. In this tutorial MS Access 2002 was used. An MS Access project will be associated with a SQL Server. For this tutorial an MSDE desktop server will be used, called XPHTEK/Test.

When the project is created the first thing that comes up is the Microsoft SQL Server Database Wizard as shown in the next picture. Make sure at this point that the server is running. If it is not, you can start the server from the Services in Administrative Tools which you should find in the Control Panel. You may want to change the SQL Server by clicking on the drop-down. The name of the database that will be created on the SQL Server will be the Project name with the suffix "SQL" appended, in this case, parametersSQL.

When you click on the Next button it will take you to the screen informing you that the program has enough information to create the database. If you click on the Finish button on this screen it creates the project as shown in the next picture. The Table object comes up in default, but for the picture shown it has been changed to Queries to show that you can create a function, a view or a stored procedure on the database you just now created.

Now you can see this database in the SQL Server Management Studio as shown in the next picture.

The project in MS Access at present does not have any tables except for some wizards which may let you create the various objects by guiding you through the process, including tables. But the parametersSQL database has all the system related tables whose owner is dbo. In order to work with this database, three tables were imported from the Northwind.mdb (from the project file's menu drop-down, click on Get External Data-->Import...). Import data which must be on most of the machines which have MS Access.

Now this project has the three tables. This automatically adds the three tables to the MSDE Server named Test. You may verify this in the Management Studio by looking up parametersSQL. You may need to refresh the database's node to see the added tables.

Creating a Stored Procedure

Under Objects in the Project click on Queries. This shows the various tasks that you can carry out. Now click on Create stored procedure in designer. This opens the  Stored Procedure1:Stored Procedure interface as shown in the next picture.

It comes up with a default template, StoredProcedure1 with an AddTable which shows the various tables in the database (it shows only the three that were imported). Now highlight Products and click on the Add button. This adds the Products table to the designer as shown in the next picture. Close the AddTable window.

From the Products table you choose the columns that are to be added to the Stored Procedure as shown. You just need to place a mark on the check box next to the column name.

Now make a right click on an empty area in the design pane right next to the table. This opens up the context-sensitive help menu as shown. You may use this to add more tables, insert a group by clause, hide the pane or look at the properties of the stored procedure.

Click on the Properties menu item which opens the next window as shown. This is the window where you can fill in all the details of your stored procedure. Since the stored procedure will be created on the MSDE Server, it should follow the various considerations such as naming convention, ownership, permissions, and so forth. You also need to specify the stored procedure parameters calling the procedure, the status of execution, and other statements that are included in the procedure. This window that is shown in the next picture provides you with all this support in a single interface.

A Stored Procedure Without Parameters

The screen shown above has three tabs. The Stored Procedure tab shows the options with some of them grayed out since the Group By option was not chosen first. The name of the Stored Procedure is also frozen as StoredProcedure1. This can be changed when you save the stored procedure as will be shown when we save this stored procedure.

There are other options such as Output all columns, Distinct values, and Set NOCOUNT on. The first two items do not require explanation as they mean what they say they are. Count in the context of a stored procedure in Transact SQL refers to the number of rows affected by the TSQL Statement. When you Set NOCOUNT on, the count is not returned. This gives a performance boost because the interchange of messages between the client and the server is greatly reduced. The Set NOCOUNT permission defaults to users.

Now without making any changes to this, exit from this screen and save the StoredProcedure1 as BasicSP (it was saved as BasicSingleParam but later renamed to BasicSP).  This is a parameter-less procedure. You could look at the SQL from MS Access' View menu drop-down, SQLView, which shows the code shown in the next paragraph. The selections made from the table are included together with the Order By clause.

ALTER PROCEDURE dbo.BasicSP
AS SELECT ProductID, ProductName, 
SupplierID, QuantityPerUnit, 
UnitPrice, UnitsInStock
FROM dbo.Products
ORDER BY ProductName

The stored procedure can be run from the project's main screen by double clicking the stored procedure icon for the BasicSP stored procedure, which shows all the items in the select query packaged in the stored procedure as shown.

Modifying the BasicSP for a stored procedure which takes one input parameter

In the design view in the row for SupplierID for the column criterion, type the character ? and step out. It immediately changes to "=@Param1". This means you have created a parameter called @Param1. Notice that a filter symbol appears in the table. This is similar to creating a parameter query in MS Access. If want to have a greater number of parameters you may do so in this screen.

Now right click on an empty area and bring up the properties page for the BasicSP stored procedure. In the Stored Procedure Parameters tab you will see the @Param1 name. It also shows it is of type int. You may give a default value of, for example, 2.

The next picture shows the Data tab of this window. We make no changes to this as we are only dealing with one table.

This has changed our BasicSP. The SQL code from the View-->SQL View is as shown in the next paragraph.

ALTER PROCEDURE dbo.BasicSP
(@Param1 int = 2
)
AS SELECT ProductID, ProductName, SupplierID, 
QuantityPerUnit, UnitPrice, UnitsInStock
FROM dbo.Products
WHERE (SupplierID = @Param1)
ORDER BY ProductName

Now if you close this window it gets saved to the same name, BasicSP. If you want to save it with a different name you have to do so from the project's File menu. The next window shows the result of running this altered stored procedure from the project's main window as described earlier.

The result of running this procedure, which takes the built-in parameter, is as shown below:

Now go back to the property of the stored procedure BasicSP, remove the default value and close out of that screen. Save the StoredProcedure as BasicOneParam.

The SQL View of this is as shown below. Since an existing stored procedure was modified, the Alter Procedure data modification statement was created.

ALTER PROCEDURE dbo.BasicOneParam
(@Param1 int)
AS SELECT ProductID, ProductName, SupplierID, 
QuantityPerUnit, UnitPrice, UnitsInStock
FROM  dbo.Products
WHERE (SupplierID = @Param1)
ORDER BY ProductName

Notice the change between the previous SQL and the one above. The @Param1 has no default value. When you double click the BasicOneParam icon in the queries window, you will bring up the window which is asking you the value of the Param1 as shown in the next screen.

For the value 7 for the Param1, the following window shows up.

This has now become a parameter query which takes one parameter. Every time you run this procedure from main window in MS Access project you will be asked to enter the parameter.

Does MS Access Project support the directionality of the parameters?

The stored procedures may take an input parameter and return the output of the procedure in another parameter. This is called the parameter's direction property. The Stored Procedure interface we saw earlier does not show any possible features which appear to be related to the "direction" of the parameter. The direction of the parameter may be inserted into the SQL statement and saved. But this is not available in the project as a feature of the wizard. You may place this under Tips and tricks. It is a tip but not a trick.

Now go back to the design view of the stored procedure and add a question mark for the Product name. When you step out, @Param2 will have been added to the stored procedure as shown.

The SQL of this stored procedure is shown next. Since the MS Access Project does not support returning the result of the procedure in a parameter, you will have to modify the SQL statement for this procedure and save. The saved stored procedure was named BasicInOut. The modified SQL is as shown in the next paragraph.

DECLARE @RC int
DECLARE @Param1 int
DECLARE @Param2 nvarchar(50)
-- Set parameter values
EXEC @RC = [parametersSQL].[dbo].[BasicInOut] @Param1, 
@Param2 OUTPUT

Since this stored procedure is not supported by the project interface, the result of executing this stored procedure can be seen in the Query Analyzer by using the proper parameter input.

Now to test run this query we run it for an input parameter, @Param1='7'. When this query is run you will get the following output. As we have seen earlier, for the SupplierID=7 multiple outputs are possible, out of which only one of them is shown. In order to see all the values you will have to modify the SQL. This is outside the scope of this article, as it's more than likely that Microsoft Project does not seem to support returning the result in a parameter.

Summary

The tutorial shows the steps involved in creating stored procedures using MS Access 2000 with the back end SQL 2000 Server. It describes three kinds of stored procedures including procedures that return values in parameters. The interface does not support the parameter "out" directionality. The SQL View, however, can be used to create such a procedure, although it cannot be tested within MS Access. Microsoft did not support Access Projects in 2003, however it supports it in Access 2007, which is still in beta.