Why is one of my SQL tables not allowing me to update in spreadsheet view in MS Access?

Question

Situation:

• I have quite a few tables created in SQL
• These tables are linked to MS Access
• I can very easily "add" new entries to all but one of the tables
  • This table includes a Foreign key reference (but so do others)
• All tables are created the same way and linked the same way

Problem:

• I cannot add entries in the Spreadsheet View in Access. Generally you have some sort of entry like (where there is a * as well as empty row beneath the table you can click in and begin typing)

  • 
• However this table looks like:
  • 
• Right clicking on a record has "New Record" and "Delete Record" grayed out, while I can use this on other tables

I am creating the table using:

CREATE TABLE ProjectApprovers (
    ProjectCode varchar(50) FOREIGN KEY REFERENCES ProjectCodes(ProjectCode),
    RACFApprover varchar(50)
);

The reason I am confused is that it does not appear to be a SQL permissions problem because I can run the following code in Access:

INSERT INTO ProjectApprovers (ProjectCode,RACFApprover) VALUES ('ValidProjectCode','test123');

It seems these restrictions are only limited to the spreadsheet view. Additionally, identical syntax is used to create other tables which do not have this problem.

I am using this code to link my database tables.

Is something like this a permission problem? I have never referenced this problem table with permissions.

Answer 1

If Access doesn't recognize a primary key in the linked table, it will present the table as read-only in Datasheet View.

Fix this by adding a primary key in SQL Server. Then recreate the link in Access so it can notice the changed table structure.