Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

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.

Show that Identity Column is identified as an AutoNumber field

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.

Identity Column not correctly identified as AutoNumber

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.

Row showing fields that say 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:

Error indicating that record is deleted.

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.

Icon Experts (brain, gears)

Ask the experts

Connect with experts, discuss the latest news, updates, and best practices, and read our blog.

Microsoft Tech Community

Icon Community

Get help in the community

Ask a question and find solutions from Support Agents, MVPs, Engineers, and other Office users.

Office Forum on Answers

Icon feature request (light bulb, idea)

Suggest a new feature

We love reading your suggestions and feedback! Share your thoughts. We're listening.

Provide feedback

See Also

Fixes or workarounds for recent issues in Access

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×