2

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?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
BT_SO
  • 25
  • 1
  • 3
  • Access does not have triggers like MSSQL does. Access uses events, usually on a Form or Report that you can the do data manipulation. Does the software link the Access tables directly in order to do the insert or how exactly is that accomplished? – random_answer_guy Jul 12 '16 at 14:30
  • @random_answer_guy - Access 2010 and later does have event-driven [data macros](https://support.office.com/en-us/article/Create-a-data-macro-b1b94bca-4f17-47ad-a66d-f296ef834200) which operate at the table level and behave somewhat like triggers. – Gord Thompson Jul 12 '16 at 14:47
  • @GordThompson - Correct, I am using MS Access 2010. Regarding the insert, the software (Pro-Server EX) has an action that is configured to use a pointer file (in this case, another .accdb file) to teel it what database, table, and field to write the data to. The software then generates and executes the INSERT statement in the background, so there's no way to modify it without changing the software. Which will take some time, but we have a customer who is looking for a solution right now. – BT_SO Jul 12 '16 at 15:26

1 Answers1

1

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.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Thanks for the information, that worked perfectly. I added two additional data macros on the Main table (one After Insert and one After Update) that delete the matching record from the Journal table. That should (hopefully) keep the size of the Journal table in check. Plus the customer can use the Journal table as a sort of transaction log if any data comes up missing. I've never used the data macros before in MS Access (or Access that much either), so your description and screen capture helped A LOT. Thanks again. – BT_SO Jul 12 '16 at 17:57