Saved Procedures
Access database engine when in query mode ANSI-92 supports CREATE PROCEDURE (SQL DDL) syntax, for example.
CREATE PROCEDURE GetCompanies ( :company_type VARCHAR(30) = NULL ) AS SELECT company_registered_number, organisation_name, company_type FROM Companies WHERE company_type = IIF(:company_type IS NULL, company_type, :company_type);
Thus, the resulting object is PROCEDURE and stored in the database file along with the tables. The focus here is on the word “stored” (not “procedure”), that is, “close to data”. Using these objects contributes to a good separation of the front end (FE) from the rear end (BE), and I mean the logical, not the physical; for example, the SQL code stored in the VBA code or in the properties of the Access Forms control is not "close to the data" and mixes the "back" layer with the "front end" level and makes it difficult to maintain the SQL code, for example, if you need to rename a column to a table, then the task will be easy if all you have to do is view PROCEDURE and VIEW s.
Another advantage of using PROCEDURE is (or rather was) that in combination with user level security (ULS) this can help usability. To use the example, people often ask how to add the created_date column to the table and save its value. Adding DEFAULT current timestamp only allows you to part with
CREATE TABLE Entities ( entity_ID CHAR(8) WITH COMPRESSION NOT NULL UNIQUE, CONSTRAINT entity_ID__pattern CHECK (entity_ID NOT ALIKE '%[!0-9]%'), entity_name VARCHAR(20) NOT NULL, CONSTRAINT entity_name__whitespace CHECK ( entity_name NOT ALIKE ' %' AND entity_name NOT ALIKE '% ' AND entity_name NOT ALIKE '% %' AND LEN(entity_name) > 0 ), created_date DATETIME DEFAULT NOW() NOT NULL );
But this does not interfere with the explicit value, which is not the current timestamp. Of course, we could add a CHECK constraint or validation rule to ensure this:
ALTER TABLE Entities ADD CONSTRAINT entity_created_date__must_be_current_timestamp CHECK (created_date = NOW());
The problem is that CHECK constraints and validation rules are checked at the row level, i.e. if you ever tried to change another column, this restriction will bite. Not good, so:
ALTER TABLE Entities DROP CONSTRAINT entity_created_date__must_be_current_timestamp;
What to do? Well, one approach is to remove privileges from the table so that end users (and applications in this context are also users) could not INSERT or UPDATE table data directly, and then create PROCEDURE to allow data modification and instead provide the appropriate privileges PROCEDURE eg
CREATE PROCEDURE AddEntity ( :entity_ID CHAR(8), :entity_name VARCHAR(20) ) AS INSERT INTO Entities (entity_ID, entity_name, created_date) VALUES (:entity_ID, :entity_name, NOW()); EXECUTE EXECUTE AddEntity '00000001', 'Black';
I use the past tense because, as you know, the Access command (or is it the SharePoint command? :)) removed the ULS from the ACE mechanism new to Access2007. I'm not sure I can recommend using an obsolete function.
Now the bad news. Many (most?) People would argue that such a PROCEDURE not a procedure, and they have a good point, because Access's SQL syntax does not support flow control, variable declaration, even the ability to execute more than one SQL statement. In other words, a PROCEDURE cannot contain procedural code. Consider a table that references Entities:
CREATE TABLE FlyingEntities ( entity_ID CHAR(8) WITH COMPRESSION NOT NULL UNIQUE REFERENCES Entities (entity_ID) ON DELETE CASCADE ON UPDATE CASCADE );
It would be nice to have PROCEDURE , which can create a string in Entities and possibly create a string in FlyingEntities based on the parameter value, but this is simply not possible in a single SQL statement. Consequently, the Access PROCEDURE database engine is of limited value, especially now that ULS has disappeared.
Triggers
Do not get around the fact that the Access database engine does not have and never had triggers. The question is, what do you need them for?
Despite the fact that I maintain the simplicity of the Access database engine, the truth is that many years ago I translated all the “serious” work into more “industrial power” and more compatible with SQL Standard products, primarily SQL Server. However, in SQL Server, I only use triggers for two things, both of which can be done without triggers (to some extent) in the Access database engine.
The first of these usages is to make sure that SQL Server CHECK restrictions do not support subqueries; in other words, they can be levels at the column and row level, but not at the table level. The restrictions on access to the CHECK database introduced in Jet 4.0 and still present in ACE (2007) are always table levels ... well, they are in theory. There is a problem (suspicious error) where they are checked at the row level, when they should be logically checked at the level of SQL queries. They do not support the SQL-92 DEFERRABLE , so there is no workaround for this problem (by the way, SQL Server suffers from the same problem when using FUNCTION to bypass the restriction on the absence of subqueries). Not all CHECK restrictions will face this problem, but its existence worries me.
The second and final use of triggers in SQL Server is for me a different limitation: the scary "FOREIGN KEY ... can cause loops or several cascading paths" when trying to create two REFERENCE to the same key, for example. this is allowed in the Access database engine:
CREATE TABLE Marriages ( entity_ID_1 CHAR(8) WITH COMPRESSION NOT NULL UNIQUE REFERENCES Entities (entity_ID) ON DELETE CASCADE ON UPDATE CASCADE, entity_ID_2 CHAR(8) WITH COMPRESSION NOT NULL UNIQUE REFERENCES Entities (entity_ID) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT cannot_marry_yourself CHECK (entity_ID_1 <> entity_ID_2) );
But port this to SQL Server (uninstall WITH COMPRESSION , etc.) and it is forbidden. In this case, cannot_marry_your will prevent loops, but SQL Server does a simple count and decides that 1 + 1 = too much. I think raw but effective. Using triggers is the only way to get a satisfactory workaround; CASCADE referral actions are a particular pain with triggers.
On the other hand, the Access database engine is in some ways even deeper than SQL Server, due to the fact that it does not attempt to detect discovery cycles at all. If you create a loop, you will not receive any warnings, and the result will be a race to overwrite the latest data and a difficult situation for debugging.
Apart from these customs, I avoid triggers because they are a maintenance headache (if you can get them in the first place). I lost track of the time when colleagues asked me for help, where we were both confused about the fact that the problem could only be for them to shyly tell me that a trigger later appeared that they forgot that they created.
So yes, there are no triggers in the Access database engine, but you may find that you might be better off without them.
Oh, and don't get me in the documentation for the Access database engine. It is fragmented, and many of these fragments disappeared over time, and many of them did not exist, in the first place, for example, I mentioned the limitations of CHECK above, but no details were published, but just a few erroneous examples (all that I know about the CHECK limitations that I had to study by trial and error) that exists that I didn’t stumble upon but ?!) And the fragments that exist contain material errors and omission errors ... even mistakenly detailing the functionality that never existed ! for example, the CREATE TABLE Statement from Access2007 Help mentions temporary tables with NOT NULL and several NOT NULL columns, all of which do not exist but are not referenced by DEFAULT or the fact that some CONSTRAINT not implemented using indexes. But the most serious IMO omission is a reference to expressions for the Access database engine, for example. IIF() behaves differently than IIF() in VBA, but this is currently undocumented. SQL Help for Jet 3 had this list, with no version since then, and Jet 3 help disappeared from MSDN a year or two ago. The lack of good documentation really affects the reliability of access to the Access database.