Access Trigger?

[dreampolice]

I looked on Google and other places and still cant find out if Access 2003 has Triggers. Can you create a Trigger in Access 2003??

 

[jrbarnett]

You can't with the MDB file format, as the Jet database engine doesn't support it.
If you are using an MSDE backend with an Access project (ADP file), you can.

John

 

[BNPMike]

It depends precisely what you mean. Jet doesn't have triggers at the table level but Access has triggers at the form level.

 

[PHV]

Sorry to disagree BNPMike.
Access (.mdb) has events at form level but no trigger at all.
Executing any action query in a form's module raises no other action.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mhartman1]

FYI:

Database trigger

http://en.wikipedia.org/wiki/Database_trigger

Regards
Mark

 

[genomon]

I think you can still define cascading behavior in MSAccess Table Relations, which can serve the same pupose as triggers in SQL Server.

Outside of a dog, a book is man's best friend. Inside of a dog it's too dark to read.

 

[JoeAtWork]

I think you can still define cascading behavior in MSAccess Table Relations, which can serve the same pupose as triggers in SQL Server.

But only if the desired trigger action is to merely update or delete related records indiscriminately.

But a database "trigger" is basically a stored procedure, and thus can do a whole lot more.

 

[SBerthold]

You can still define a type of "trigger" in MS Access ...
They are just not internal to the db table, but need to be ran by a SQL statement in MS Access.

A trigger is nothing but a function to do something, and this function is called when action is taken on a piece of data.

When executing a SQL Statement in the MS Access Query window, you can almost validate anything in any [other] table or can cause restrictions to prevent changes, or cause other changes under certain conditions.

So, if you create a Query for all db Tables, then you can add trigger functions for them, and allow use of only the queries.

So, in a sense, yes you can create triggers in MS Access.

Create in a Module a public function and compile it
Sample:

Option Compare Database
Option Explicit

Public Function MyTriggerForQry1(MyArgument As String) As Boolean
    Dim db As DAO.Database
    Set db = CurrentDb
    
    Select Case UCase$(MyArgument)
        Case "001" 'Update another Table
            db.Execute "UPDATE AnotherTable Set SomeOtherfield='Hi There Again!' WHERE SomeOtherfield='" & MyArgument  & "'"
        Case "002" 'Allow a change in the record
            MyTriggerForQry1 = True
        Case Else 'No changes allowed under some condition
            MyTriggerForQry1 = False
    End Select
End Function

Create a query in the Query window that uses the function ():

UPDATE SomeTable SET SomeField ='Hi There'
WHERE  (MyTrigger(SomeField)=True)

For this, change to SQL view in the query window and add the code, and then back to Design view to see how to use it there.

 

[SBerthold]

I had made a last minute change in the function, and format to change the query:

UPDATE SomeTable SET SomeField ='Hi There'
WHERE  (MyTrigger[b]ForQry1[/b](SomeField)=True)