CODEWITHSUNDEEP
asp.netms-access
Vivekh
Vivekh

Reputation: 4259

Trigger in MS Access

Can I insert triggers in MS Access as in other databases like SQL Server?

Upvotes: 4

Views: 2390

Answers (5)

Michael
Michael

Reputation: 11

Although there are no triggers in ms access like sql server, you have creative options. One is setting table property formats allowing/restricting certain data. Another is the firing of update queries when a user is about (before update event) send data from interfact to tables. Of course there is using ms access just as a front end connected to a client server database instead of the flat file system of ms access (oracle and sql server are common backends for an access front end). Good luck.

Upvotes: 0

Tunde Ajao
Tunde Ajao

Reputation: 1

This has been answered already. MS Access database unlike the server Databases does not have triggers, stored procedures or packages. However, there are ways round this and if you want to be notified anytime an insert, update or delete is done to the database then you could add an select query via the VBA Code. Or you could add a procedure to an audit table with the Time, type of change, and values if required.

Access 2007 triggers and procedures equivalents?

You could have a MS Access front end and use a MS SQL Server back end but to some people this might be cheating. But you could use VBA code to change the values and use some of the queries on the tables.

Upvotes: 0

Parfait
Parfait

Reputation: 107737

Loosely speaking, Access VBA subroutines/functions are what Stored Procedures are to SQL Server, Triggers to MySQL, Procedural Functions to PostgreSQL. Likewise, queries would correspond to views.

With that said, do note Access VBA comes equipped with the full Jet SQL language. You can create tables, update, append, make-tables, create queries, create recordsets all dynamically with parameters in logical routines. In a sense, you do not need to use the ribbon at all to create or manage your database. Moreover, stored queries can even use VBA functions directly in its script: SELECT table1.A, table1.B, somefunction() from table1.

The challenge would be to translate your server trigger event into a user trigger event. Essentially, the user must do something for tables to be updated, queries run, etc. So if you express your event with some user added/updated value, you can script a VBA function to run routines on a specific tables or multiple tables.

Upvotes: 1

Johnny Bones
Johnny Bones

Reputation: 8404

You can put an OnTimer event on your form and have it launch a macro or module at a given time or interval. This will work for every version of Access back to A95.

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91376

Are you using Access 2010? If so, you can use Access 2010 data macros*, otherwise you are out of luck.

Access 2010 data macros (similar to triggers)

by Access Team on August 13

Last week we outlined improvements to the Access 2010 macro designer, parameter support, and IntelliSense. These improvements build upon Access 2007 macro improvements of macro sandbox, embedded macros, TempVars, debugging, and support for error handling.

The natural next step in macros is to provide a model for business rules. Data macros allow developers to attach logic to record/table events (similar to SQL triggers). This means you write logic in one place and all forms and code that updates those tables inherit that logic. Here are a few data macro scenarios you might find in a typical Donations Management database:

Upvotes: 6

Related Questions