Using the MS Access stored procedure in VB. NET

In the latest MS Access, Microsoft has made every effort to make this product a truly full-featured relational database system. Stored Procedures, a feature typically related to enterprise database systems such as SQL Server, can now be found in access. Since Access2000, access already has the stored procedure function and is provided by the JET 4 database engine. If you are used to using stored procedures in SQL Server, you will be familiar with how they are used in access. But remember that there are some limitations. I will discuss it later.

This articleArticleIt is divided into two parts. The first part describes in detail how to create a stored procedure in access using ADO. NET and Visual Basic. net. The second part demonstrates how to use the stored procedure created in the first part to create a data access layer andProgram. In this articleCodeIt has passed the access2002 test, and even so, it should be able to run on Access2000.

How does a stored procedure work in access?

Unlike other objects in access, the stored procedure does not have a user interface and cannot be created on the access interface. The method to create them is only encoded. I will demonstrate how to implement the Code in ADO. net.

When a stored procedure is added to the Access database, Jet Engine converts the stored procedure to a query object. For an access developer, writing a simple query is unnecessary. However, it does have its advantages. Consider the situation that an application system must be divided into two systems to separate the access and SQL Server versions. Using Stored Procedures makes it easier to write code for the database access layer of the application, and there is little difference between different versions of the program.

Create a stored procedure

To demonstrate how to use SQL statements to create a stored procedure. At the end of the article, I will list the code for all statements to be executed in the database. When using the northwind database that is included with access, it creates four stored procedures. They are all concentrated on the prodcut table, so that we can start with the easiest one: Select data for all rows in the table. To create this stored procedure, run the following SQL statement in the database:

"Create proc procproductslist as select * from products ;"

Statement "create proc proccustomerlist" is part of the actually created stored procedure. The section below "as" can be any valid SQL statement.

Frequently, you need to pass parameters to the stored procedure for query. For example, you may want to delete a record based on a productid. The following stored procedures show how to do this:

"Create proc procproductsdeleteitem (inproductsid long )"&_
"As Delete from products where productsid = inproductsid ;"

In the first line, pay attention to the parentheses on the right side of the create proc declaration. There is a parameter defined as a long integer. This is the variable you need to input to delete records.

The following two separate statements show how to create an add and an update stored procedure for the product table. Note that for the sake of short articles, the process does not include all fields:

"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;

Note: When the number of parameters exceeds one, separate them with commas.

Restrictions

Here, you may encounter some restrictions, especially if you are very accustomed to the powerful functions of SQL Server.

Output parameters cannot be used.

Do not use the @ character. The @ character is usually used in transaction SQL (SQL Server), representing a local variable. Access does not always convert this character, and sometimes it is omitted. This problem may lead to a hard-to-find bug, which may cause you to lose a few more hairs.

Access cannot access temporary tables.

I suspect that many options in transaction SQL do not exist in access because they are not compatible with transaction SQL.

Conclusion

I hope this article will provide some guidance in an undisclosed area of access, but I have not discussed more about the jet engine. For more information about how ADO. Net code works in the createstoredproc subroutine, see getting started with ADO. Net written by gurneet Singh. Download all the article code from the download section.