MS Access trigger?

Question

I have two tables named [Insert_Record] and [Delete_Record] in MS Access. Both tables have the same fields but one table has records whereas another table has no record.

Question: I want, whenever I delete any record from the table [Insert_Record] that entire record should automatically insert into another table, i.e: [Delete Record].

How can I accomplish this?

Tropaeolin

you can refer to this link: office.microsoft.com/en-us/access-help/… – Westmoreland

page has no content!! – Minima

Answer 1

Access 2010 introduced event-driven Data Macros that are similar to triggers. The process described in the question can easily be done with an After Delete data macro on the [Insert_Record] table:

Arak

Answer 2

As I understand it, Access doesn't have triggers.

The best you can probably do is put this sort of logic into the forms that edit the table. In other words, handle the deleted event at the form level and put your insert logic there.

If you want triggers, you'll want to use a proper RDMS that supports them (MySQL, MS SQL, Oracle, many others).

EDIT: Another way to do this (which may or may not work for you) would be to add a boolean column 'IsDeleted'. That way, you can just logically delete a record instead of moving it to another table. The downside of this approach is the deleted records stay in the main table which could cause performance woes if there are lots of deletes.

Cowl

Up until last year you are correct however the latest version of access (2010) does having a thing that is close to a trigger. As the OP did not say what version he is on it is safe to assume its an old version so this does not apply. Anyway here is a link blogs.office.com/b/microsoft-access/archive/2009/08/13/… – Scad

I would say triggers are irrelevant here. There is a design error in that data is being stored in two tables, when it should all be stored in the same table, with a field suitably configured to distinguish the records that were previously stored in different tables. – Spadix

@Spadix Soo, add an IsDeleted column? – Cowl

Whatever you call it, and whether it's a Boolean or a field that can store more than two different values, yes -- you define an attribute of each record to distinguish the different groups that were previously in multiple tables. – Spadix

@Spadix This is not a design flaw. It's called a tombstone table and is a common way of tracking deleted rows when synchronization to external sources is required. One can argue that these rows don't belong in the table because they don't really exist; they're just an implementation detail specific to synchronization. They certainly aren't relevant to any result set on queries to the table. You could also argue that in that case, one need only track the primary keys and possibly a date, but I believe it's common to keep the whole row. – Benilda

Answer 3

Create an append query, to add records into the second table, that is executed in the On Delete Confirm event of the form you are using to delete the record from the first table.

Monobasic

Answer 4

I imagine this 'Delete' is button driven...

So program the button to first Insert the record into one table before deleting it.

Just add the VBA for the insert above the delete.

Another method which nullifies your need for another table entirely is just one column that is a boolean... Active Yes/No (Default to yes.)

If Active then it is not actually deleted when they hit delete, just set the flag to False then nothing is actually deleted, no SQL code is required and there is no risk, you can even have a column with the user who updated it print in another column

   Me.Active = False
   Me.UserName = 'CurrentUser Location here
   Me.RecordSet.Requery

Then there is no risk of actually losing anything is there and you have a record of who flagged it last.

In the continuous form just add the Where flag Active = True then no false flags will show and it looks to the user base as if it was deleted.

Codfish