1

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.

1 Answer 1

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.