CREATE PROCEDURE Statement (Microsoft Access SQL)

Office 2013 and later

Last modified: March 09, 2015

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:

SQL
Copy 
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.

VBA
Copy 
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: