CREATE PROCEDURE Statement (Microsoft Access SQL)

Last modified: June 29, 2011

Applies to: Access 2013 | Office 2013

In this article
Syntax
Remarks
Example

Creates a stored procedure.

Note 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.

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.

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.

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.

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 

Show:
© 2015 Microsoft