Working with Stored Procedures in an MS Access Project

(Page 1 of 4 )

This 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.

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.

Next: Creating a Stored Procedure >>
 

More Microsoft Access Articles
More By Jayaram Krishnaswamy

Comments On: Working with Stored Procedures in an MS Access Project

· Using stored procedures you are buying in security so that your code is safe from...    · Hi,I liked your article on stored procedures. I was wondering if we can use...    · Hi,I also have the same issue. I right now have application which uses Tables...   >>> Post your comment now!