CREATE PROCEDURE Statement (Microsoft Access SQL)

Access Developer Reference

Creates a stored procedure.

Bb177892.vs_note(en-us,office.12).gif  Note
The Microsoft Access database engine does not support the use of CREATE PROCEDURE, or any of the DDL statements, with non-Microsoft Jet database engine databases.
Syntax

CREATE PROCEDURE procedure     [param1 datatype[, param2 datatype[, …]] AS sqlstatement

The CREATE PROCEDURE statement has these parts:

Part Description
procedure A name for the procedure. It must follow standard naming conventions.
param1, param2 From one to 255 field names or parameters. For example:
  CREATE PROCEDURE Sales_By_Country [Beginning Date] DateTime, [Ending Date] DateTime;

For more information on parameters, see PARAMETERS.

  CREATE PROCEDURE Sales_By_Country [Beginning Date] DateTime, [Ending Date] DateTime;
datatype One of the primary Microsoft Access SQL data types or their synonyms.
sqlstatement An SQL statement such as SELECT, UPDATE, DELETE, INSERT, CREATE TABLE, DROP TABLE, and so on.
Remarks

An SQL procedure consists of a PROCEDURE clause that specifies the name of the procedure, an optional list of parameter definitions, and a single SQL statement.

A procedure name cannot be the same as the name of an existing table.

Example

This example names the query CategoryList.

This example calls the EnumFields procedure, which you can find in the SELECT statement example.

  Sub ProcedureX()
Dim dbs As Database, rst As Recordset
Dim qdf As QueryDef, strSql As String

' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")

strSql = "PROCEDURE CategoryList; " _
    & "SELECT DISTINCTROW CategoryName, " _
    & "CategoryID FROM Categories " _
    & "ORDER BY CategoryName;"

' Create a named QueryDef based on the SQL
' statement.
Set qdf = dbs.CreateQueryDef("NewQry", strSql)

' Create a temporary snapshot-type Recordset.
Set rst = qdf.OpenRecordset(dbOpenSnapshot)

' Populate the Recordset.
rst.MoveLast
        
' Call EnumFields to print the contents of the 
' Recordset. Pass the Recordset object and desired
' field width.
EnumFields rst, 15

' Delete the QueryDef because this is a
' demonstration.
dbs.QueryDefs.Delete "NewQry"

dbs.Close

End Sub

See Also

ADD USER Statement

ALTER TABLE Statement

ALTER USER or DATABASE Statement

CONSTRAINT Clause

CREATE INDEX Statement

CREATE TABLE Statement

CREATE USER or GROUP Statement

CREATE VIEW Statement

DROP Statement

DROP USER or GROUP Statement

GRANT Statement

REVOKE Statement