Access does not recognize the Identity Column in a linked SQL Server table

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.

UserVoice for Office

See Also

Fixes or workarounds for recent issues in Access

Need more help?

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×