none
The 1912 update to Access 2016/2019/365 breaks the way linked SQL Server tables with identity columns are attached RRS feed

  • Question

  • Repro

    1. Create a table with an identity primary key in SQL Server, e.g. 

    CREATE TABLE identitytest (id int identity(1,1) primary key, a nvarchar(10), b nvarchar(10));

    2. Create a new MS Access database and attach the table as a linked table (External Data/New Data Source/From Other Sources/ODBC Database/Link.../Create DSN/Choose table).

    3. Open the table in design view.

    Expected result (all Access versions before 365 v1912): The identity field is shown as an "AutoNumber" field:

    Actual result on Access v1912 (Build 16.0.12325.20288) and higher: The identity field is shown as a "Number" field:

    (Note that this issue can be reproduced with both the old MDAC {SQL Server} ODBC Driver and the new {ODBC Driver 17 for SQL Server}.)




    Now, this might seem like an insignificant detail, but it affects how MS Access "finds" new records: When Access "recognizes" an AutoNumber field, it can just query @@IDENTITY. This is what happens in SQL Server Profiler when I add a new record through a bound Access UI:

    exec sp_executesql N'INSERT INTO  "dbo"."identitytest"  ("a","b") VALUES (@P1,@P2)',N'@P1 nvarchar(10),@P2 nvarchar(10)',N'a',N'b'
    SELECT @@IDENTITY
    exec sp_prepexec @p1 output,N'@P1 int',N'SELECT "id","a","b"  FROM "dbo"."identitytest"  WHERE "id" = @P1',1


    When Access does *not* recognize the AutoNumber field, it performs a "full row comparison" instead:

    exec sp_executesql N'INSERT INTO  "dbo"."identitytest"  ("a","b") VALUES (@P1,@P2)',N'@P1 nvarchar(10),@P2 nvarchar(10)',N'a',N'b'
    exec sp_prepexec @p1 output,NULL,N'SELECT "id","a","b"  FROM "dbo"."identitytest"  WHERE "id" IS NULL'
    exec sp_executesql N'SELECT "dbo"."identitytest"."id" FROM "dbo"."identitytest" WHERE "a" = @P1 AND "b" = @P2',N'@P1 nvarchar(10),@P2 nvarchar(10)',N'a',N'b'
    exec sp_prepexec @p1 output,N'@P1 int',N'SELECT "id","a","b"  FROM "dbo"."identitytest"  WHERE "id" = @P1',1

    Apart from the obvious performance drawbacks, this causes legacy forms to break under certain circumstances (the dreaded "#DELETED" issue).

    So, can one of you MVPs please conform this issue and forward it to Microsoft, so that we can have it fixed? Thanks!

    In the mean time, suggestions for workarounds (e.g. how to hack the "cached" linked table schema with code to fix the id field data type) are welcome.

    Thanks, best regards
    Heinzi



    • Edited by Heinzi.at Thursday, January 16, 2020 1:35 PM
    • Changed type Heinzi.at Thursday, January 23, 2020 1:12 PM
    Thursday, January 16, 2020 1:09 PM

Answers

All replies

  • From my understanding it has been confirmed and is on their radar.  Hopefully, someone else can provide more details on the matter, but I did forward it along.

    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Thursday, January 16, 2020 1:47 PM
  • All I can say is post the issue on access.uservoice.com, send the information through the Access Feedback button, call Microsoft support (especially if you are an enterprise customer!), post the question in every forum you are a member of, make noise!  The golden rule is true: "The squeeky wheel gets the grease!"

    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Thursday, January 16, 2020 4:56 PM
  • YES, I have this exact problem, however is reflected to me in different ways

    • when creating new records using code DAO in a loop and no longer can retrieve the AutoNumber
    • when creating new records in a form and can no longer retrieve the created AutoNumber
    • The issue with legacy forms is critical and the code feedback is not clear

    I was able to fix my apps by moving some code to the SQL side and provide less code in the access side and change some logic, the result was improved performance.

    Suddenly after the update there were all my users calling me because some forms with over 20 years of created started failing in all my deployments.

    • Edwin Blancovitch
    • www.easypayroll.net


    Thursday, January 16, 2020 5:26 PM
  • The issue was introduced in Office update/version 1912.  The recommendation at this point in time is to rollback to version 1911, or to switch to the Semi-Annual channel (which is at version 1908) until MS figures this all out.

    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Thursday, January 16, 2020 6:44 PM
  • Microsoft has finally publish a page on the subject, so feel free to revert to it

    https://support.office.com/en-us/article/access-does-not-recognize-the-identity-column-in-a-linked-sql-server-table-ae418bbf-2658-453a-82f1-7e043812d60d


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Friday, January 17, 2020 12:59 AM
  • In the heat of today's flurry of activities around this issue I forgot this: THANK YOU for the EXCELLENT detective work you did. It certainly helped focus the efforts. Much appreciated!

    (some of your fellow countrymen farming along the Danube also spoke highly of you - wink)


    -Tom. Microsoft Access MVP

    Friday, January 17, 2020 3:44 AM
  • I can confirm the issue with new or refreshed tables in my own Access 365 applications.

    Luckily I mainly use A2010 which isn't affected

    As an experiment I created a new A2010 database and linked several SQL tables with autonumber fields.
    I then reopened that app in A365 - the autonumber fields were still correct (as long as you don't refresh the links in A365)

    If you do that in reverse - link in A365 then open in A2010 THEN refresh the links, the autonumber datatype is once again correctly identified
    So that's an additional work-round that may help some people


    • Edited by isladogs52 Friday, January 17, 2020 4:28 PM
    Friday, January 17, 2020 4:09 PM
  • This is the second really bad bug that Microsoft has recently introduced into Access via updates to Office 365.

    I have been recommending to clients that they either buy retail Office/Access or slow down the default update process of Office 365. I expected Microsoft to blow it occasionally, but they're doing worse than I would have anticipated.

    My clients didn't get bitten by this bug, for the reason above, but thank you for reporting this.


    • Edited by rusticloud Sunday, January 19, 2020 10:57 PM
    Sunday, January 19, 2020 10:54 PM
  • Hi,

    the fixing build 12325.20344 has been out since yesterday:

    https://docs.microsoft.com/en-us/officeupdates/monthly-channel-2020#version-1912-january-22

    You should get it within the Office update cycle or actively pull it with "Update now".


    Karl
    http://www.AccessDevCon.com
    http://www.donkarl.com

    Thursday, January 23, 2020 11:58 AM
  • the fixing build 12325.20344 has been out since yesterday:


    https://docs.microsoft.com/en-us/officeupdates/monthly-channel-2020#version-1912-january-22

    You should get it within the Office update cycle or actively pull it with "Update now".

    What a nice surprise! A big THANK YOU from me to the people at Microsoft who fixed this so quickly and, of course, to all you MVPs for keeping us regular users in the loop.

    Best regards
    Heinzi

    Thursday, January 23, 2020 1:11 PM