MS Access and Stored Procedures

Question

Does MS Access support stored procedures on .accdb or .mdb databases? Or are stored procedures only supported when the backend is SQL Server or something else that supports an Access DB engine (Jet / ACE/ MSDE)?

Information is mixed and not quite complete from what I have found on MS Access and Stored Procedures from various sources. For example this Access help file initially looks like Access supports stored procedures.

https://support.microsoft.com/en-us/office/create-procedure-statement-91c700d1-8076-4040-896a-a0b7cf9d9888?ns=msaccess&version=90&ui=en-us&rs=en-us&ad=us

When I try to run the following query, it errors saying I have incorrect syntax for CREATE TABLE statement. The table is in an .accdb file on my local laptop.

CREATE PROCEDURE GetReport  (pFileId Integer) 
AS 
SELECT * FROM FileInfoTable WHERE FileId = pFileId

Does this answer your question? [How do I make a stored procedure in MS Access?](https://stackoverflow.com/questions/3287545/how-do-i-make-a-stored-procedure-in-ms-access) — June7, Nov 17 '21 at 18:43
I had seen that post before. It seems to me that is really a macro and not really a stored proc. — stymie2, Nov 17 '21 at 21:14

score 2 · Accepted Answer · answered Nov 17 '21 at 19:55

Some Access DDL features are only available when the statement is executed with ADO. Your statement will succeed if you execute it from CurrentProject.Connection.Execute as this Immediate window example demonstrates:

strSql = "CREATE PROCEDURE GetReport (pFileId Integer)" & vbCrLf & _
     "AS" & vbCrLf & _
     "SELECT * FROM FileInfoTable WHERE FileId = pFileId"
CurrentProject.Connection.Execute strSql

However notice that the SQL text in the saved query object does not exactly match the text in the CREATE PROCEDURE statement. But I think it's functionally equivalent.

Debug.Print CurrentDb.QueryDefs("GetReport").SQL
PARAMETERS pFileId Long;
SELECT *
FROM FileInfoTable
WHERE FileId = pFileId;

I ended up using this method. It allowed me to call the Access stored procs almost the same way as I was with the SQL Server stored procs. Since the program could use Access, SQL Server or Both it was convenient. I used SQL components for SQL Server and OleDB components for Access. Cast objects to the common db base types. And was able to make much of it generic. — stymie2, Jan 05 '22 at 18:47

score 1 · Answer 2 · edited Nov 18 '21 at 04:32

Well, yes, access does have what is called table triggers, and access also has what we call stored procedures (procedural code that can run at the table level - or in fact you can even call such stored procedures from VBA, or macros).

However, just like MySQL, SQL server, Oracle and more? They ALL HAVE their OWN type of code system for these stored procedures. Access is no different.

This feature and ability was introduced in Access 2010. However, this feature, this ability has ZERO to do with the SQL supported CREATE procedure command.

That command was introduced to give "some" compatibility with SQL Server syntax - but it is quite lame, and limited. WHEN you use CREATE PROCEDURE in Access SQL, it ONLY creates a select command. As such, the select command is JUST THAT - ONLY a select command and is NOT procedural code.

However, as noted, you can write stored procedures in Access, and they run by the data engine, and they run at engine level. To write these routines, you have to use the macro language. This language is NOT VBA, and not T-SQL (SQL Server code), but Access has its OWN syntax and code.

These so called table routines are thus created when working on a table in design mode.

While such code is limited, it is REAL procedural code, does not use SQL syntax in most cases, and such routines can be called from table events.

And these so called "data macros" are not created with SQL data create commands, you are limited to using the built in macro editor for this purpose.

Keep in mind that while Access has this stored procedure code ability, they are not transaction based. But, they can be of GREAT use, since they even function when using OLEDB or ODBC sources to Access.

So, if you write such code, and then say open Access with FoxPro, or even c# in .net? And then you update a row? The procedural table code and triggers WILL run.

You can see these options here:

So, I could for example maintain the total cost of an invoice for each change, or edit, or adding of rows to a sub form.

The macro language editor looks like say this:

So you can loop over data, you have if/then else etc.

It is a very limited language - but they are in fact true real stored procedure code that runs at the table level, and they run 100% independent of VBA or any other code.

And these table stored procedures run:

when edit data in a form - even forms without code
when you issue SQL update or modify statements
when you modify data via VBA + recordset code
if an external ODBC or OLEDB source such as .net c# or whatever modifies data

One correction, when you use CREATE PROCEDURE you can do more than just select statements. I ended up doing INSERT and UPDATE also. — stymie2, Dec 08 '21 at 22:32
Thanx for the additional knowledge, this may come in handy in the future. For my project I accepted the first response since that was the way I ended up going. I am matching 1 for 1 the Access procs with Sql Server procs since it will use both. It was easier (although still quirky) to use the Create Procedure command through the VBA Immediate window. I am using C# .NET to access Access and SQL Server. Proc names and parameters have to match exactly since I am casting oledb and Sql components to db components for the generic proc execution. — stymie2, Dec 08 '21 at 22:42
Yes, while the newer version of JET dataengine (called ACE) does in fact have store code now, and true/real table triggers? Those so called data macro routines cannot be create, changed or even moifed with SQL (DDL statements), you have to use Access to create and modify these data triggers. Once created, then any data source and any SQL statements used will fire those table code routines (no matter where and what software platform you use). However, as noted, - no externa means to create or setup or modify the new table code feature exists outside of the Access UI. — Albert D. Kallal, Dec 08 '21 at 23:37

MS Access and Stored Procedures

2 Answers2