Is is possible to update SQL Server 2016 temporal tables from Access?

Question

I have created a SQL Server 2016 database with a temporal table Product. I would like to update the table from an Access database through a linked table. When attempting to do this, Access reports

Reserved error (-7776): there is no error message for this error

The table is defined as:

CREATE TABLE [dbo].[Product]
(
    [Product] [uniqueidentifier] NOT NULL,
    CONSTRAINT PK_Product_Product PRIMARY KEY CLUSTERED (Product),
    [Name] [nchar](50) NOT NULL,
    CONSTRAINT [AK_Product_Name] UNIQUE([Product line], [Name]),
    [Status] [uniqueidentifier] NOT NULL,
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)     
) ON [PRIMARY] WITH    
   (   
      SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory)   
   ) 
GO

The query

UPDATE Product 
SET Status = (SELECT [Product status] 
              FROM [Product status]
              WHERE [Name] = 'Announced') 
WHERE [Name] = 'A300';

succeeds without error and is shown in the Access table. Updates are correctly reflected in the History table.

I have tried hiding the valid time columns in case their back-end change causes the problem, but with no effect. Both Product and Product status tables contain minimal data.

Is there some specific way to get this to work, or is this scenario not supported?

Answer 1

The default precision in the datetime2(7) columns exceeds the precision available in Access. When the precision is reduced to datetime2(3) the query becomes updateable.

Even this may be too high as there may only be a little over 2 digits available for the fractional seconds. When linking the table, no message is reported to indicate the effective representation is insufficient to represent the column.

The following update from SQL Server Profiler demonstrates the problem in the values of the Valid time fields

exec sp_executesql N'UPDATE "dbo"."Product" 
    SET "Product status"=@P1  
    WHERE "Product line" = @P2 AND "Product" = @P3 AND 
        "Name" = @P4 AND "Product status" = @P5 AND "SysStartTime" = @P6 AND 
        "SysEndTime" = @P7',
    N'@P1 uniqueidentifier,@P2 uniqueidentifier,@P3 uniqueidentifier,@P4 nvarchar(50),@P5 uniqueidentifier,@P6 datetime2,@P7 datetime2',
    '3C...1E4B','38...2883','8E...0387',N'A300','44...6B76','2016-08-09 21:43:07.8710000','2016-08-09 22:45:59.1340000'

Where the following shows the value of the row before the update - which then fails because of the insufficient precision of the Access columns.

Product line Product   Name Product status SysStartTime                SysEndTime
38...2883    8E...0387 A300 44...6B76      2016-08-09 21:43:07.8709730 2016-08-09 22:45:59.1342223

This is related to the range problem in BIGINT columns in Rows showing as #DELETED. A comprehensive mapping of the newer SQL Server types would be helpful.

The GUIDs have been partially elided to make the output more readable.