CODEWITHSUNDEEP
sql-serverms-accessms-access-2007
John Cooper
John Cooper

Reputation: 11

Access ODBC linked table to SQL Server allows inserts but not updates

Based on Web readings, I built a new ODBC connection, carefully looking for subtle configuration parameters that might suggest fostering updates, but none were found. Then I tested the new link.

To Re-Test my issue:

1) I created the following table on SQL Server 2005:

[TestTbl]

column1: Key      Type:Integer
Column2: Name     Type:varchar(5)

Populated as follows

   Key     Name
   ===     ====
    1      Apple
    2      Bear
    3      Cat

2) Then in Access 2007, created a link to the SQL Server table TestTbl using my latest ODBC connection.

3) Next successfully inserted the following new records into the SQL Server table using the link and executing my inserts from Access 2007:

   Key     Name
   ===     ====
    4      Dog
    5      Elephant

4) Finally I tried to execute the following simple update query:

   UPDATE dbo_TestTable SET dbo_TestTable.TestName = "CatNip"
   WHERE (((dbo_TestTable.TestKey)=3));

I got the error message "Operation must be an updateable query"

5) Out of frustration, I inserted another record

   Key     Name
   ===     ====
    6      Nonsense

Then I posted this question asking for help.

Can anyone please explain why I can insert new records to the linked table but I cannot update existing records?

Upvotes: 1

Views: 2580

Answers (1)

Turcogj
Turcogj

Reputation: 161

The problem you are having is because there is either no primary key defined or when you linked the table in Access the Primary key was not defined. Re-add the linked table (delete and add) and select a primary key field, in this case column1

Upvotes: 2

Related Questions