Cannot modify *some* MSSQL Server linked tables from Access 1

[ESquared]

I don't know if this should be here or in the Access forum. Please let me know if I should post this elsewhere.

I have an MS SQL server with a database that I'm connecting to with Access 97 via a linked table and a System DSN.

Some of the tables I have full access to: I can add, delete, and edit records.

Some of the tables I don't: I can read them but when I try any edit or change I receive the message "This Recordset is not updatable."

I have clicked every possible thing in SQL Server Enterprise Manager to find some difference between the working tables and the non-working tables, and I cannot. I create new tables and they cannot be changed.

I used an ADODB connection from a Module and it was able to insert a row:

[tt]StrConnString = "DRIVER={SQL Server};SERVER=SQL1;DATABASE=MyDB;Uid=sa;Pwd=frootloops"

conC.Execute "INSERT INTO MyTable (MyField) VALUES (1);", lngResult, adExecuteNoRecords[/tt]


Does anyone have any idea? This is maddening. Two other people here also cannot figure out what the problem is, and especially why it is for some tables and not others.

 

[RobertT687]

A few things come to my mind.
1) Do you have permission in MSSQL to modify the linked table?
2) Make sure the linked tables have a promary key defined in MSSQL. Access requires that a table have a primary key for it to be updatable.
3) Is the linked object a table or a view? If it is a view, it's likely it cannot be updated

 

[ESquared]

1) I'm sure that I have permission
2) You may be on to something here and I will investigate it further!
3) It is not a view.

Thank you for your input.

 

[ESquared]

Thank you very much, RobertT687.

I had considered whether the problem was the lack of a primary key, before, but did not realize that Access inspects this primary key presence only at table-linking time, so was thrown off in my testing.

That is, if you link a table and then add a primary key on the MSSQL side, Access still will not let you update the table because it does not know what keys to use to uniquely identify each record.

Similarly, if you link a table that has a key, and then remove the keys in MSSQL, Access can still write to the table because it is using its initial assumptions about what field(s) is/are the primary key(s).

 

[josephwalter]

I've had problems with the Link Table Manager in Microsoft Access forgetting the primary keys when you use it to refresh a linked table from SQL Server.

(just sharing an experience, in case you fall into it too)