MS-Access query can't update SQL 2005 table via odbc.

[kiwipeet]

Hi.

I'm using SQL Server 2005 as a back end, with MS-Access 2003 as a front end is attached via ODBC linked tables.

MS-Access is used mainly for queries and a couple of forms. I now want to update data in the table via the form.

I can query data, and insert data without any problems, but I can't update. Can anybody explain why? is this a common problem?

I am trying to update a single row and set a date. The query looks like this:
update linked_sql_table
set date_done = date()
where key_value = 'ABC123';

The error message is: "Operation must use an updateable query".

The end users have sufficient priviledges (grant all to users) and access the data using windows authentication. (and I am dbo... and can't do it.)

Alternatively, is there a better way of doing this?

For example: could I place the sql in a stored procedure and execute it with the key value as a variable? (or am I looking for a complex solution to a simple problem?)

 

[SQL_Hell]

Does the table have a primary key defined?

The correct syntax for updating a column to the current datetime in sql server is:

set date_done = getdate()

 

[joshd]

make sure that the tables in sql have indexes and keyes.
I an into the same problem and that "cured" the malady. Also note that if there are any Bit data type fields in sql, they should be set to a default value (0 for NO/False and -1 to Yes/True) Access does not like null values for this type of fields while sql sets them to null.

 

[kiwipeet]

Traced issue to missing primary key definition. The sql table had a primary key but access was unaware of it.

Apologies for taking 6 years to reply

 

[Rx_]

Apology accepted.... if you go to Thread tools and mark it as "Solved"
Your post has had a lot of views. That would be a major closure!
LOL

B.T.W. Every so often, I migrate a local temp table to SQL and totally forget to add an index. It happens more often than it should given how many times I have seen this obscure error.

 

[kiwipeet]

Will do. Can't do that from phn.