Sample Header Ad - 728x90

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

1 vote
1 answer
359 views
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.
Asked by Lennart - Slava Ukraini (23862 rep)
May 18, 2021, 09:12 AM
Last activity: May 20, 2021, 06:43 AM