Access 2007 triggers and procedures equivalents?

Question

Ok, does anyone have some good resources for Access 2007 features regarding triggers or stored procedures? Can it even do them or something similar to them? Every resource I have found on Microsoft’s help is referencing back to Access 2003, as well as many of the help manuals online. Everything is moved around in 2007, so it is a little tough to translate older help manuals. I really wanted to use ms sql, but got forced to do this small project in access, so any resources would be helpful.

Cool, all of the answers so far have been helpful. Just wanted to confirm a lot of the scattered knowledge of access I have. I think I can make it work for this project. Oh, and I cannot use sql due to lots of...red tape.

Answer 1

Stored procedures

The Access database engine when in ANSI-92 Query Mode supports the CREATE PROCEDURE (SQL DDL) syntax e.g.

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);

The resulting object is therefore a PROCEDURE and is stored in the database file along with the tables. The emphasis here is on the word 'stored' (rather than 'procedure') i.e. it is 'close to the data'. Use of these objects encourages good separation of the front end (FE) from the back end (BE) and I mean logical rather than physical; for example, SQL code stored in VBA code or in the properties of an Access Forms control is not 'close to the data' and mixes the back end 'layer' with the front end 'layer' and makes the maintenance of SQL code more difficult e.g. if you need to rename a column in a table the job is easy if all you need to do is look through the PROCEDUREs and VIEWs.

Another advantage of using a PROCEDURE is (or rather, was) that when coupled with user level security (ULS) it can help 'usability'. To employ an example, it is often asked how to add a created_date column to a table and maintain its value. Adding a DEFAULT of the current timestamp gets you only part the way there e.g.

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 doesn't prevent an explicit value that isn't the current timestamp. We could of course add a CHECK constraint or Validation Rule to enforce this:

ALTER TABLE Entities ADD
   CONSTRAINT entity_created_date__must_be_current_timestamp
      CHECK (created_date = NOW());

The problem here is that CHECK constraint and Validation Rules get checked at the row level i.e. if you ever tried to change another column the constraint would 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 the privileges from the table so that end users (and applications in this context are users too) cannot INSERT or UPDATE the table's data directly, then create PROCEDUREs to allow the data to be changed and instead grant to appropriate privileges to the PROCEDUREs e.g.

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'm using the past tense because, as you may be aware, the Access team (or was it the SharePoint team? :)) removed ULS from the new-for-Access2007 ACE engine. I'm not sure I can recommend using a deprecated feature.

Now the bad news. Many (most?) folk would argue that such a PROCEDURE is not a procedure and they've got a good point because the Access database engine's SQL syntax does not support control-of-flow, variable declaration, even the ability to execute more than one SQL statement. In other words, a PROCEDURE cannot comprise 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 be have a PROCEDURE that can create a row in Entities and optionally create a row in FlyingEntitiesbased on a parameter value but this just isn't possible in a single SQL statement. Therefore, an Access database engine PROCEDURE is of limited value, especially now that ULS has disappeared.

Triggers

There's no getting around the fact that the Access database engine does not have, and has never had, triggers. The question is, though, do you need them?

Though I maintain a fondness for the simplicity of the Access database engine, the truth is that many years ago I move all 'serious' work into more 'industrial strength' and more SQL Standard compliant products, primarily SQL Server. However, in SQL Server I use triggers for only two things, both of which can be done without triggers (to a certain extent) in the Access database engine.

First of these usages is to cope with the fact that SQL Server CHECK constraints do not support subqueries; put another way, they can be column-level and row-level but not table-level. Access database engine CHECK constraints, introduced in Jet 4.0 and still present in ACE (2007), are always table-level... well, they are in theory. There is a problem (suspected bug) where they are checked at the row level when they should be logically checked at the SQL statement level. They do not support the SQL-92 DEFERRABLE syntax, therefore there is no workaround for this problem (incidentally, SQL Server suffers from the same problem when using a FUNCTION to workaround the no subqueries limitation). Not all CHECK constraints will run into this problem but its existence makes me a little wary.

Second and final usage for triggers in SQL Server for me is due to another limitation: the dreaded "FOREIGN KEY...may cause cycles or multiple cascade paths" when attempting to create two REFERENCEs to the same key e.g. 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 (remove the WITH COMPRESSION etc) and it is not allowed. In this case, the cannot_marry_yourself will prevent cycles but SQL Server does simple counting and decides that 1 + 1 = too many. Crude but effective, I suppose. Using triggers is the only way satisfactory workaround; the CASCADE referential actions are a particular pain with triggers.

On the other hand, the Access database engine in a way is even dumber than SQL Server in this regard because it makes no attempt the detect cycles at all. If you do create a cycle, you get no warning and the result will be a race to overwrite the data last and a difficult situation to debug.

Other than these usages, I avoid triggers because they are a maintenance headache (if you can get them right in the first place). I've lost count of the times colleagues have asked me for help where we've both been flummoxed as to what the problem might be only for them to sheepishly tell me later there was a trigger they'd forgotten they created.

So, yes, the Access database engine lacks triggers but you may find you may be better off without them.

---

Oh, and don't get me started on the documentation for the Access database engine. It is fragmented and many of those fragments have disappeared over time and many didn't exist in the first place e.g. I mentioned CHECK constraints above but there has never been any details released, merely a couple of flawed examples (everything I know about CHECK constraints I had to learn by trial and error -- what exists that I haven't stumbled upon yet?!) And the fragments that do exist contain material errors and errors of omission... even erroneously detailing functionality that has never existed! e.g. CREATE TABLE Statement from the Access2007 Help mentions temporary tables, named NOT NULL constraints and multi-column NOT NULL constraints, all of which do not exist, but fails to mention DEFAULT or the fact that some CONSTRAINTs are not implemented using indexes. But the most serious omission IMO is the reference for the Access database engine expressions e.g. IIF() behaves differently than IIf() in VBA but this seems to be currently undocumented. The SQL Help for Jet 3 had such a list, no version since has and the Jet 3 help disappeared from MSDN a year or two ago. The lack of good documentation really dents the Access database engine's credibility.

Answer 2

As for triggers, if you are using an access data project, then you don’t have local tables and you not even using jet. In this case triggers will be designed created and written in SQL server. Do keep in mind that when you build an access data project, you can’t use any other database server except for SQL server. Most versions of office and access do have a version of SQL server on the CD for this purpose. This has changed for 2007, but nevertheless in this case you can’t use local tables with access data projects.

So if you choose to use an access ADP you will by default have triggers available.

If you’re using a standard mdb or accDB file and are not using SQL server but using JET (now called ACE) then you’ll not have triggers available.

Answer 3

Stored procedures are basically done as queries in Access. Pretty much any documentation for 03 will hold true to 07 as the functional differences are quite sparse.

Answer 4

According to wikipedia:

Microsoft Access is a file server based database. Unlike client server RDBMS, Microsoft Access does not implement database triggers, stored procedures, or transaction logging.

Were the resources you found for 2003 talking abour ADP files? I think they may be, in which case may be relating to triggers/procedures in an SQL Server backend which is what they are geared up for.

Answer 5

In Access, there is no such thing as a trigger. This applies to all versions.

Answer 6

Consider the possibility of using Access 2007 as a front end for SQL Express. If your problem domain is something that Access JET could handle, SQL Express can also handle it and you get things like triggers and stored procedures "for free". The closest thing that native Access/JET has to stored procedures are queries (action and standard) and there is nothing like a trigger in native Access/JET.

There isn't much challenge is setting up the Express editions either, and Access works great as a front end for SQL Express. You won't notice much difference (except the style of the table designers and such) when working with such a back end, and you avoid having to do it when your application scales up to need a real database server anyway.