Last Updated: January 23, 2020
ISSUE
When creating a link to a table in SQL Server that has an Identity Column, Access will normally identify that column as an AutoNumber column.
In Current Channel builds (16.0.12325.*) of Version 1912 of Office 365 and Office 2016/2019, Identity Columns are not correctly identified, and will instead be recognized only as a Number.
This will happen when creating new links through the user interface or through code, but only for new links. Existing links will not be affected, unless the link is Refreshed.
Access uses the proper identification of an Identity Column for a number of purposes, so this can impact applications in several ways.
For example, an Identity Column is normally used to identify a newly added record. When the Identity Column is not used, Access may be unable to correctly find the newly added row, which will cause the fields in that row to show as #Deleted.
You may see similar issues when using the RecordSet.LastModified property to retrieve values for the last added record. The following code:
Set rst = CurrentDb().OpenRecordset("tblWithIdentityColumn")
rst.AddNew
rst!CompanyName = "Contoso"
rst.Update
rst.Bookmark = rst.LastModified
MsgBox rst!CompanyName
May result in error 3167:
In both cases, the record will be successfully added, but Access will fail to find it successfully.
Access will also normally ignore attempts to update the Identity Column directly. However, in these builds it allows the attempt but fails and generate an error message:
[SQL Server] Cannot insert explicit value for identity column in table 'tblOrders' when IDENTITY_INSERT is set to OFF.(#544)
STATUS: FIXED
The fix for this issue is now available for Version 1912. You may need to explicitly force an update to receive the fix. The fixed build number should be 12325.20344.
If you are on Insiders Slow (Version 2001) or Insiders Fast (Version 2002). The fix is not yet delivered in those channels, so you may want to move to Current Channel.
Note: After you change versions, you must refresh any affected links to ensure that they behave correctly again.
Ask the experts
Connect with experts, discuss the latest news, updates, and best practices, and read our blog.
Get help in the community
Ask a question and find solutions from Support Agents, MVPs, Engineers, and other Office users.
Suggest a new feature
We love reading your suggestions and feedback! Share your thoughts. We're listening.
