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
modifed_by columns access views it as a descrepencies and sends out an ugly error prompt?
The users like seeing the
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?