Export (0) Print
Expand All

CREATE PROCEDURE Statement (Microsoft Access SQL)

Office 2007

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:

PartDescription
procedureA name for the procedure. It must follow standard naming conventions.
param1, param2From 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;
datatypeOne of the primary Microsoft Access SQL data types or their synonyms.
sqlstatementAn 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



Community Additions

ADD
Show:
© 2014 Microsoft