How do I make a stored procedure in MS Access?

Question

How do I make a stored procedure in MS Access?

Answer 1

Access 2010 has both stored procedures, and also has table triggers. And, both features are available even when you not using a server (so, in 100% file based mode).

If you using SQL Server with Access, then of course the stored procedures are built using SQL Server and not Access.

For Access 2010, you open up the table (non-design view), and then choose the table tab. You see options there to create store procedures and table triggers.

For example:

Note that the stored procedure language is its own flavor just like Oracle or SQL Server (T-SQL). Here is example code to update an inventory of fruits as a result of an update in the fruit order table

Keep in mind these are true engine-level table triggers. In fact if you open up that table with VB6, VB.NET, FoxPro or even modify the table on a computer WITHOUT Access having been installed, the procedural code and the trigger at the table level will execute. So, this is a new feature of the data engine jet (now called ACE) for Access 2010. As noted, this is procedural code that runs, not just a single statement.

Answer 2

If you mean the type of procedure you find in SQL Server, prior to 2010, you can't. If you want a query that accepts a parameter, you can use the query design window:

 PARAMETERS SomeParam Text(10);
 SELECT Field FROM Table
 WHERE OtherField=SomeParam

You can also say:

CREATE PROCEDURE ProcedureName
   (Parameter1 datatype, Parameter2 datatype) AS
   SQLStatement

From: http://msdn.microsoft.com/en-us/library/aa139977(office.10).aspx#acadvsql_procs

Note that the procedure contains only one statement.