Stored Procedure in MS 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.

When a stored procedure is added to an Access Database, the Jet Engine reworks the stored procedure syntax into a query object.

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:

"AS DELETE FROM Products WHERE ProductsID = inProductsID;"

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:

"Values (inProductName, inSupplierID, inCategoryID);"

" WHERE ProductID = inProductID;"

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.
• Temporary tables are not available in Access.
• I suspect many of the options available in Transact SQL are not available in Access as it's not Transact SQL compatible.

Imports System.Data

Imports System.Data.OleDb

Module CreateSP

Sub Main()

ProductsProcs()

End Sub

' Products Stored Procs to be added to the db.

Sub ProductsProcs()

Dim sSQL As String

' procProductsList - Retrieves entire table

sSQL = "CREATE PROC procProductsList AS SELECT * FROM Products;"

CreateStoredProc(sSQL)

' procProductsDeleteItem - Returns the details (one record) from the

' JobTitle table

sSQL = "CREATE PROC procProductsDeleteItem(@ProductID LONG) AS " _

& "DELETE FROM Products WHERE ProductID = @ProductID;"

CreateStoredProc(sSQL)

' procProductsAddItem - Add one record to the JobTitle table

sSQL = "CREATE PROC procProductsAddItem(inProductName VARCHAR(40), " _

& "inSupplierID LONG, inCategoryID LONG) AS INSERT INTO " _

& "Products (ProductName, SupplierID, CategoryID) Values " _

& "(inProductName, inSupplierID, CategoryID);"

CreateStoredProc(sSQL)

' procProductsUpdateItem - Update one record on the JobTitle table

sSQL = "CREATE PROC procProductsUpdateItem(inProductID LONG, " _

& "inProductName VARCHAR(40)) 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 local

' system.

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 Sub

End Module