Others

Fix Access Linked SQL Server Tables Showing Number Instead of AutoNumber

Olivia Miller Oct 9, 2026 869 views

Question details

The user needs to correct a field data type discrepancy where Microsoft Access interprets a linked SQL Server primary key as a Number rather than an AutoNumber.

Fix Access Linked SQL Server Tables Showing Number Instead of AutoNumber

Product: Microsoft Access / SQL Server
Device & OS: not provided
Scenario: Relinking redesigned SQL Server tables to Microsoft Access for database querying and management.
Observed behavior: The linked primary-key field displays as Number instead of AutoNumber in Access, causing append and delete queries to fail.

Before you start

Ensure you have administrative privileges in SQL Server Management Studio (SSMS) to modify table designs, and verify that no users are actively querying the database.

Solution 1Recommended

Set the SQL Server Identity Property and Relink in Access

Configure the primary key correctly in the SQL Server backend and refresh the linked tables in Access so the database engine recognizes the AutoNumber format.

Microsoft Access relies on the SQL Server 'Identity' property to recognize a field as an AutoNumber. If this property is missing, Access treats the primary key as a standard Number, which breaks row modification queries like append or delete.

Modify the table in SQL Server

Open SQL Server Management Studio (SSMS), right-click your redesigned table, and select 'Design'. Select your primary key column and ensure its Data Type is set to 'int'.

Enable the Identity Specification

In the Column Properties window at the bottom, expand 'Identity Specification'. Change the 'Is Identity' dropdown to 'Yes', then save the table design.

Remove old table links in Access

Open your Microsoft Access database. Go to the 'External Data' tab, click on 'Linked Table Manager', select the outdated SQL Server tables, and delete their links.

Relink the updated SQL tables

Still in the 'External Data' tab, click 'New Data Source' > 'From Database' > 'From SQL Server'. Follow the ODBC wizard to reconnect and relink your updated tables. Access will now properly identify the primary key as an AutoNumber.

Set the SQL Server Identity Property and Relink in Access

Query Functionality Restored: Once Access recognizes the AutoNumber format, you can successfully run your append and delete queries again.

Free Microsoft Office alternative

Try WPS Office for Your Daily Productivity Needs

While you use database tools for data management, WPS Office provides a free, lightweight, and complete alternative for your document, spreadsheet, and presentation tasks. Enjoy a familiar interface and seamless compatibility with Microsoft Word, Excel, and PowerPoint files.

1. Download the software: Visit the official WPS Office website and click the free download button for your operating system.
2. Install WPS Office: Run the downloaded installation file and follow the quick on-screen prompts to set up the suite.
3. Start creating and editing: Open WPS Office to seamlessly view, edit, or create your documents, spreadsheets, and presentations in one unified workspace.

Fully compatible with Microsoft Word, Excel, and PowerPoint formats.Built-in PDF toolkit to easily convert, edit, and share your exported database reports.Lightweight software that installs quickly and runs smoothly on all your devices.Familiar tabbed user interface requiring zero learning curve for traditional Office users.

microsoft office alternative - wps office

Frequently Asked Questions

Why do my append and delete queries fail in Access linked to SQL Server?

This failure usually occurs because the SQL Server primary key is not configured with the Identity property. As a result, Access reads the key as a standard Number instead of an AutoNumber, preventing the engine from properly tracking and modifying row identities during append and delete operations.

Do I need to relink the tables every time I change the SQL Server table design?

Yes. Microsoft Access caches the schema of linked tables locally. If you alter column data types, add new columns, or change the Identity property on the SQL Server side, you must use the Linked Table Manager in Access to refresh or recreate the connection.

Can I add the Identity property to an existing column using a simple SQL query?

In SQL Server, you cannot simply alter an existing column to turn on the Identity property using an ALTER TABLE statement. You must either use the SSMS table designer (which recreates the table in the background) or manually create a new table with the Identity column, migrate your data, and drop the old table.

Office Quick Solutions