Edit

CREATE PROCEDURE statement (Microsoft Access SQL)

  • Article

Applies to: Access 2013, Office 2013

Creates a stored procedure.

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

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, and 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