Access| stored procedure 1. How does a stored procedure run in Access?
Unlike other objects in Access or MS SQL that can have an intuitive design interface, there are no stored procedures in access, so we can't build them in access, and I'll show you how to manipulate them in ado.net.
2. Creating a Stored Procedure
We need to use a SQL statement to create the stored procedure, and we use the case database Northwind to illustrate our example.
A simple stored procedure
"CREATE PROC procproductslist as SELECT * from Products;"
Create PROC procproductslist means creating a stored procedure as the following can be any valid SQL statement.
But there are times when we need to set up a parameter, such as we want to delete the record of the specified Productsid, then we need such a stored procedure. "CREATE PROC Procproductsdeleteitem (inproductsid LONG)" & _
"As DELETE from the products where productsid = Inproductsid;" In giving a more complex:
"CREATE PROC Procproductsadditem (inproductname VARCHAR)," & _
"Insupplierid Long, Incategoryid long" & _
"As INSERT into the products (ProductName, SupplierID, CategoryID)" & _
"Values (Inproductname, Insupplierid, Incategoryid);" CREATE PROC Procproductsupdateitem (Inproductid LONG, "& _
"Inproductname VARCHAR" & _
"As UPDATE products SET ProductName = Inproductname" & _
"WHERE ProductID = Inproductid;" Well, the principle has been known. We put these together to make a module, not better, say dry.
Imports systemimports system.dataimports System.Data.OleDbModule createsp sub Main () productsprocs () end Sub ' Products Stored procs-added to the Db. sub Productsprocs () & Nbsp; dim sSQL as string ' procProductsList- Retrieves entire table ssql = "CREATE PROC procproductslist as SELECT * from the products; createstoredproc (sSQL) ' Procproductsdeleteitem-returns The details (one record) from the & nbsp; ' jobtitle table ssql = ' CREATE PROC Procproductsdeleteitem (@ProductID LONG) as "_ & "DELETE from the products WHERE ProductID = @ProductID;" createstoredproc (sSQL) ' Procproductsadditem-add one record to the JobTitle table ssql = "CREATE PROC procproductsadditem (inproductname VARCHAR)," _ & "Insupplierid long, Incategoryid long) as INSERT into" _ & nbsp; & "Products (ProductName, SupplierID, CategoryID) Values" _ & "(Inproductname, Insupplierid, categoryid); " createstoredproc (sSQL) ' Procproductsupdateitem-update one record on the JobTitle TABLE     &NBsp; ssql = "CREATE PROC procproductsupdateitem (Inproductid LONG," _ & nbsp; & "Inproductname VARCHAR" 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 ' SYSTEM.&NBSp; 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 SubEnd Module
(To be continued)