After Insert Macro Triggered by an Append Query to Update a Linked Table

[M_S_Jones]

Hi All,

I'm trying to update a value in an SQL Server linked table on insert of a value into an Access table in my linked back end. I've setup an after insert macro on my Access back end table which calls a function to update the linked SQL Server table using DoCmd.RunSQL.

This works absolutely fine as long as I type or paste the value into my Access table. If I try to append the value using an append query in Access I get the run-time error message 2486: "You can't carry out this action at the present time" and the DoCmd.RunSQL line is highlighted.

I'd be grateful for any suggestions anyone can offer.

Thanks,

Matthew

 

[Minty]

Try using Currentdb.Execute YourSqlQuery, dbSeeChanges instead.

 

[isladogs]

Check whether there are other fields in the SQL table which are REQUIRED values. If so ensure that those fields are also populated by your append query

 

[M_S_Jones]

Wow, thanks for the quick reply Minty! That has resolved the issue, thank you. This is now working when I append a single record. If I append multiple it appears the after insert event is only triggered for the first record. Is there a way to handle this scenario, or is this a limitation of the trigger?

 

[Minty]

Not sure on Access Triggers to be honest, I have only ever used triggers on SQL server.
Maybe you can alter your sql query to update all records after the first inserted one?

 

[M_S_Jones]

Not sure on Access Triggers to be honest, I have only ever used triggers on SQL server.

Thanks. I'll mark this thread as complete, as you've solved my original problem.

Thanks again,

Matthew