Fix Access Linked SQL Table That Is Not Updateable
Question details
The user is unable to edit data in a linked SQL Server table via Microsoft Access and receives a Recordset is not updateable error.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Attempting to update, edit, or add records in an Access datasheet or form that relies on a linked SQL Server table.
- Observed behavior
- Access blocks the user from editing the data, displaying The Recordset is not updateable, often occurring after upgrading ODBC drivers if the table lacks a primary key.
Ensure you have the necessary database administrator permissions in SQL Server Management Studio (SSMS) to alter table designs and define primary keys.
Add a Primary Key and Relink the Table in Access
Define a primary key in the backend SQL Server table and recreate the table link in Access to restore data editing capabilities.
Microsoft Access requires a unique identifier (usually a Primary Key) for linked SQL Server tables to safely identify and update individual records. If a table lacks this key, Access treats it as read-only to prevent accidental data corruption.
Open SQL Server Management Studio (SSMS), locate your database, and right-click the problematic table. Select 'Design', right-click the field that uniquely identifies each record (such as an ID column), and choose 'Set Primary Key'. Save the table design.
Open your Microsoft Access database. In the Navigation Pane, right-click the linked SQL Server table that you cannot update and select 'Delete'. This only removes the link, not the actual data in SQL Server.
Navigate to the 'External Data' tab in Access, click 'New Data Source', select 'From Database', and then 'From SQL Server'. Follow the ODBC wizard to connect to your database and select the table you just modified.
During the linking process, Access may prompt you to 'Select Unique Record Identifier'. Ensure you check the box next to the field you set as the Primary Key in SQL Server, then click 'OK'.
Looking for a Free, Lightweight Office Suite?
While WPS Office does not replace Microsoft Access for relational database management, it offers a robust, free alternative for your daily document, spreadsheet, and presentation needs. Experience seamless compatibility with Microsoft Office formats in a familiar, user-friendly interface.
Frequently Asked Questions
Why did upgrading my ODBC driver cause this linked table issue?
Older ODBC drivers might have handled tables without primary keys differently, sometimes displaying records as #Deleted rather than blocking edits completely. Newer drivers, like ODBC Driver 17, enforce stricter data integrity rules, requiring a valid primary key to allow recordset updates.
Can I update an Access linked table without a primary key?
Generally, no. Microsoft Access needs a primary key or a unique, non-null index in the linked SQL Server table to safely target specific records for updates without modifying unintended rows.
What if I do not have permission to modify the SQL Server table?
If you lack permissions to add a primary key in SQL Server, you can request your database administrator to create a unique view for your Access front-end. Alternatively, you can use a Data Definition query in Access to create a pseudo-index for the linked table, though altering the backend design is the most stable solution.
Why do my linked table records display as #Deleted?
This often occurs when using an older ODBC driver on a table without a primary key, or if the data was modified by another user while you were viewing it. Upgrading your ODBC driver and ensuring the table has a primary key typically resolves the #Deleted display error.
