Working with MS Access Stored Procedures in V…

Article source code: msaccess_sp.zip

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:

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:

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:

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.

  标签：

  版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
  特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有