Creating a Stored Procedure

Stored procedures allow you to perform bulk operations that delete, update, or append records. Unlike views, stored procedures allow the ORDER BY clause and parameters. Use the CREATE PROCEDURE (or CREATE PROC) statement to create a stored procedure. You must specify the name of the stored procedure and the AS keyword followed by the desired SQL statement that performs the required database operation. The syntax is as follows:

Programming with the Jet Data Definition Language

CREATE PROC[EDURE] procName

sqlStatement;

The name of the stored procedure must be different from the name of an existing table. If you want to pass values to a stored procedure, then the procedure name must be followed by one or more parameters. Parameter names are followed by a data type and separated by commas. The parameter list must be enclosed in parentheses (see Hands-On 23-4 in the next section). Up to 255 parameters can be specified in the parameter list. If your stored procedure does not require parameters, the AS keyword immediately follows the name of the stored procedure.

The SQL statement can be prepared using the Access Query Design tool and then copied to the VBA procedure from the SQL view and appropriately formatted.

Suppose you want to return the employee records from the vw_Employees view (see Hands-On 23-1) ordered by Full Name. You can write the following stored procedure:

CREATE PROCEDURE usp_EmpByFullName

Select * From vw_Employees

ORDER BY [Full Name];

The stored procedure above selects all columns that exist in the vw_Employees view and orders the returned data by the employee's Full Name. Notice that this procedure does not require any parameters. You might want to precede the stored procedure name with a prefix indicating the type of stored procedure. The "usp" prefix is often used to indicate a user-defined stored procedure.

Like views, stored procedures are created via the ADO Execute method after establishing a connection to the database. Therefore, we can use the following VBA code to create the usp_EmpByFullName stored procedure:

Sub Create_StoredProc()

Dim conn As ADODB.Connection

Set conn = CurrentProject.Connection conn.Execute "CREATE PROCEDURE usp_EmpByFullName AS " & _ "SELECT * FROM vw_Employees " & _ "ORDER BY [Full Name];" Application.RefreshDatabaseWindow conn.Close Set conn = Nothing

End Sub

Once created, stored procedures are visible in Microsoft Access 2003 after selecting the Queries object in the left pane of the Database window. They can be executed in the Access user interface by double-clicking the stored procedure name, or from VBA code by calling the EXECUTE statement with the ADO Execute method (see Hands-On 23-5).

Part III

0 0

Post a comment