Unable to update SQL Server via Access, Reserved error (-7776)

Question

I'm trying to use Access as a front-end for an SQL Server 2017 database. The objects I'm interested in are 6 temporal tables and a view joining them. I created an ODBC user data source and linked the tables and the view. For the view, I created an instead of update trigger that updates the underlying tables, and that works just fine.

Since the primary keys of the underlying tables are identity, I figured it would be easier to insert/delete directly against the underlying tables instead of via the view (I had to SET IDENTITY_INSERT ON when manipulating the view via SQL).

I can add a row to one of the tables without a problem, but when I try to delete or update a row in the table, I get an error saying "Reserverat fel (-7776). Det finns inget meddelande för felet". Loosely translated that would be something like "Reserved error (-7776). There is no message for the error".

I visit the world of Windows on a regular basis every 20 years or so, so I have no real clue on where to start looking for any clues on what might be the error. Any insight?

Funny thing is that if I create a view that joins two of the tables (the primary key of one of the tables is also unique in the view), I can update the view, and the underlying table is also updated (no instead of trigger necessary).

It is also possible to insert a new row (for delete it seems like I will need an instead of trigger). So I guess this is a workaround, but it seems rather odd that I can update the view, but not the table.

Answer 1

Access (I guess) does not seem too fond of SysStartTime and SysEndTime columns. If I mask them in a view and link the view in Access, both update and insert works. Example:

DROP VIEW dbo.PHYTOCLASS_DIVISION;
CREATE VIEW dbo.PHYTOCLASS_DIVISION as 
select d.DivisionName
 , c.ClassID
 , c.ClassName
 , c.DivisionID as classDivisionId
 , c.ModifiedBy
 , c.ModifiedDate
 , c.Avslutad
from dBotnia20200929.dbo.PHYTODIVISION d
join dBotnia20200929.dbo.PHYTOCLASS c
 on d.DivisionID = c.DivisionID;

For a trivial join like this, SQL Server figures out how to handle insert/update so instead of triggers are not necessary.

The answer in is-is-possible-to-update-sql-server-2016-temporal-tables-from-access indicates that the problem has to do with the precision of the timestamps in Sys... columns. I have not verified that lowering the precision would help, but it may be worthwhile for someone to read that answer