Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I make a stored procedure in MS Access?

Tags:

How do I make a stored procedure in MS Access?

like image 504
yonan2236 Avatar asked Jul 20 '10 06:07

yonan2236


People also ask

Can we create stored procedure in MS Access?

Creates a stored procedure. 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.

How do you create a procedure in Access database?

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. Keep in mind these are true engine-level table triggers.


2 Answers

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:

screenshot

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 alt text

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.

like image 62
Albert D. Kallal Avatar answered Oct 21 '22 10:10

Albert D. Kallal


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.

like image 28
Fionnuala Avatar answered Oct 21 '22 11:10

Fionnuala