Writing to a table linked from SQL Server

[VegaLA]

Hi all,
wonder if anyone can assist me with this the SQL Server
security/write (?) issue. The SQL Server Admin has created a table for our Dept. to use and via SQL Server I can get access to it using the username and password she supplied to me. I can also link to this table in Access 2002 via ODBC which is the way I am using to write to SQL server tables. I have developed an Access application to allow one of our staff to be able to write data to the SQL Server table at a click of a button, however when I open the table the 'add new record' button is greyed out, I can not add any data to the table.
I explained this problem to the Admin as well as the way in which we access the table and she is certain that the problem lies in Access but i'm unsure how that can be the case.

Has anyone experienced this problem before ? Is there a fix and if so how is that implemented ?

Should I also post this in the SQL server forum ?

Thanks in advance,
Mitch....

 

[llkhoutx]

You either don't have write permission or your form recordset is nonupdatable. Forms with queries as recordsets are non updatable by definition. Often a table filter, and ordey by with accomplish the same effect.

 

[llkhoutx]

Pat - you' ve got a false premise. Every query is not updatable.

Any form based on an updatable query is by definition updatable.

I fight this exact battle every day at the office. I deal with linked Oracle warehouse tables that have no primary key. It's a nightmare. The system administrator is a high powered accountant and doesn't have a clue.

A query can be updatable. Maybe. A form with a query as its recordsource is updatable only if an existing primary key is returned in the query result. One cannot be added.

A form with the following recordsource is updatable:

SELECT tbAccounts.AcctID, tbAccounts.sAcct
FROM tbAccounts
WHERE (((tbAccounts.AcctID)>=1));

A form with the following recordsource is not updatable:

SELECT tbAccounts.AcctID, tbAccounts.sAcct
FROM tbAccounts
WHERE (((tbAccounts.AcctID)>=5));

if no records are returned, one cannot add records. Therefore, the form is not updatable.

I've seen the rule in Litwin's Access 200 Developer's handbook, but can't find it right now.

Are we quibbling?