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?

Does your ODBC link to the temporal table use the latest ODBC driver for SQL Server? — Gord Thompson, Aug 09 '16 at 22:37
You could try to create a view on this table without the problematic columns, link that in Access and try to update records in there. — Andre, Aug 09 '16 at 22:38
@Gord Using the `SQL Server Native Client RDA 11.0.` installed with a new installation of SQL Server 2016. — Pekka, Aug 10 '16 at 07:17
Smells like a data issue, more specifically invalid data in your date columns. Maybe you could run a query to search for any invalid date values. — Rene, Aug 10 '16 at 09:22
@Rene Invalid meta-data mapping between datetime2 and DateTime - specifically the default precision is not achieveable in Access, and no warning indicated when incorporating the column into the key. — Pekka, Aug 10 '16 at 17:46

score 2 · Accepted Answer · edited May 23 '17 at 12:01

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.

This generated UPDATE sql is weird, putting all columns in the WHERE clause. Does Access recognize the primary key when you open table design of the linked table? — Andre, Aug 10 '16 at 10:12
@Andre Yes, the primary key is correctly detected. The update looks like a ["#Deleted" errors with linked ODBC tables](https://support.microsoft.com/en-us/kb/128809) fallback to try to verify the update correctness. — Pekka, Aug 10 '16 at 13:33

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

1 Answers1