PROCEDURE Clause (Microsoft Access SQL)

  • Article
Access Developer Reference

Defines a name and optional parameters for a query.

Bb208921.vs_note(en-us,office.12).gif  Note
The PROCEDURE clause has been superseded by the PROCEDURE statement. Although the PROCEDURE clause is still supported, the PROCEDURE statement provides a superset of the capability of the PROCEDURE clause and is the recommended syntax.
Syntax

PROCEDURE name [param1 datatype[, param2 datatype[, …]]

The PROCEDURE clause has these parts:

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

For more information on parameters, see parameters.

 
  PROCEDURE Sales_By_Country [Beginning Date] DateTime, [Ending Date] DateTime;
datatype One of the primary Microsoft Access SQL data types or their synonyms.
Remarks

An SQL procedure consists of a PROCEDURE clause (which specifies the name of the procedure), an optional list of parameter definitions, and a single SQL statement. For example, the procedure Get_Part_Number might run a query that retrieves a specified part number.

Bb208921.vs_note(en-us,office.12).gif  Notes
  • If the clause includes more than one field definition (that is, param-datatype pairs), separate them with commas.
  • The PROCEDURE clause must be followed by an SQL statement (for example, a SELECT or UPDATE statement).

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

DELETE Statement (Microsoft Access SQL)

EXECUTE Statement (Microsoft Access SQL)

SQL Data TypesMicrosoft Access Database Engine SQL Data Types

PARAMETERS Declaration (Microsoft Access SQL)

SELECT Statement (Microsoft Access SQL)

UPDATE Statement (Microsoft Access SQL)