SQL Server – Access Write Conflicts with Views and InsteadOf Triggers

ms accesssql server

I have a legacy linked table in a msaccess database that is linked to a sql-server table.

The linked table is being synced periodically to a different set of tables that have been split up for various reasons and have more columns than the users want to see. I'd like to get rid of the legacy table and the head aches of syncing it, by creating a view with instead of triggers to handle the data access. Let's say my view looks like:

Create view Compatibility_View
with schemabinding,view_metadata
as
select   a.Name,a.col_1 aliasedName,a.col_2 aliasedName2
        ,b.col_1 aliasNamed3 ,convert(float,b.col_2) aliasedName4
        ,a.modified_date,a.modified_by
from dbo.some a
join dbo.other b
  on a.some_Id = b.some_Id
where a.isActive = 1;

And I have instead of triggers that will soft delete the records and route the properties appropriately.

I also automatically update the modified_date and modified_by columns. (These use to be handled by after triggers on the legacy table).

Unfortunately, doing this second set of requirements causes access to issue a warning after each edit that data has been changed. Tracing the queries access issues it appears that it is doing an update to the view, making sure that all other columns haven't changed. e.g.

update "dbo"."Compatibility_View"
set "aliasedName"='v'
where "aliasedName2"=45 and "aliasedName3"='horse'
and "aliasedName4"=1.3e2 and "modified_date"='09/10/2014 12:35:00.00'
and "modified_by"='AD\SomeUser'

Since my trigger change the modified_date and modifed_by columns access views it as a descrepencies and sends out an ugly error prompt?

The users like seeing the modified_date and modified_by columns in access so removing this piece is not really an option. How can I get these columns in the view without access freaking out?

Best Answer

Access uses the values of all columns to decide if a row has been updated by some other process between Access reading the row and subsequently writing to that row.

You can prevent that behavior by adding a TIMESTAMP column to the table(s) in question. Access will then use the single TIMESTAMP value to determine if the row has been modified by someone else.

The only way to prevent that error message is to not update the row prior to Access attempting its own update. I would make Access modify the two fields currently being modified by the trigger.

Related Question