CODEWITHSUNDEEP
ms-accessms-access-data-macro
BT_SO
BT_SO

Reputation: 25

Using MS Access triggers and/or querys to either insert or update a record

I have a 3rd-party software (Pro-face Pro-Server EX) that is inserting records into a MS Access database (.accdb file extension, not a front end for a SQL database). The way the process is configured on the Pro-Server EX software side is that it sends the data over to the database as an insert only (there's no way to configure it to search for an existing record to perform an update). We have a customer that has a database with preexisting data (with an ID field that the primary key) where all he wants to do is to update the existing record based on the ID. I know of a method to do this in SQL where I would configure a trigger similar to the following:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[update_table]
 ON  [dbo].[Table1]
 INSTEAD OF INSERT
AS

DECLARE @seqno int
DECLARE @Data1 int

BEGIN
SET @seqno = (Select seqno from INSERTED)
SET @Data1 = (Select Data1 from INSERTED)

IF EXISTS (SELECT seqno FROM [dbo].[Table1] WHERE seqno = @seqno)
 BEGIN
  UPDATE [dbo].[Table1] SET seqno=@seqno,Data1=@Data1 WHERE seqno=@seqno
 END
ELSE
 BEGIN
  INSERT INTO [dbo].[Table1] (seqno, Data1) VALUES (@seqno, @Data1)
 END
END

However I do not have as much experience with MS Access querys and/or the triggers. When I attempt to copy this code into the SQL view of a MS Access query I get several errors, so it seems that this will not really an option for MS Access.

I have never used the triggers that are available in MS Access, but it appears that the closest one that can be used is the "Before Change", which is described as "Create Logic that runs before a record is saved to validate changes, and then decide to allow the new values, change the values, or show an error to stop the changes. Use the [IsInsert] property to determine if the event is an insert or an update." This description tells me that this trigger will only be tripped once the data is already written to the database, but not saved, so it really can't be used to determine if a matching ID already exists to perform an update, and if not, then perform an insert. Am I interpreting this correctly?

Is there a way to do what I am looking for in MS Access?

Upvotes: 2

Views: 5541

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123829

You are correct that a Before Change data macro will not work for the situation you describe. However, you could have the external process always INSERT into a journal table, then have an After Insert data macro on that table either INSERT or UPDATE a row in the main table like so:

AfterInsert.png

Of course, the journal table would continue to grow over time, so a scheduled maintenance job could be created to periodically delete old journal records.

Upvotes: 1

Related Questions