I'm using the Teradata 13.00.00.02 ODBC driver to connect to our Teradata server using Access 2010 (although I had similar experiences with 2007) and when I try to run an update query in Access, I recieve an error that says "Operation must use an updateable query." Our environment is set up where we have a tables database (for admin access) and a views database for user access. I've tried ODBC connections to both database with the same result. Even when I up not directly trying to update the Teradata tables, but rather use them as a reference table in a query, I get the same error.
I'm able to append to Teradata tables without any problem, it is only updating that is an issue. I've talked to other developers in the organization who all experience the same thing. They alway have to take the Teradata data and do a MAKE TABLE within Access, update it, then append it back to Teradata.
Any help is much appreciated.
I just ran a quick test with MS Access 2010 and the ODBC Driver for Teradata version 22.214.171.124. I created a linked table in MS-Access and inserted few rows to the Teradata Database; I simply opened the Linked Table and typed the rows into the Grid. Later I created a new query; changed the query from Select to Update and ran few update tests. It seems to work. Can you outline the steps to replicate the issue?
I am not sure this is correct, but I suspect that the issue is that the view you are using to access the table must be granted UPDATE rights on the underlying table.
if the view you are using is a single table only (no joins) you should be able to update the table. however when you use Views to access tables, extra rights must be granted for the right up update the underlying table.
Access when linked to a table does not always surface a useful error message to the user of the linked table.
if you wish to prove this, and have access to sql assistant or another ODBC/JDBC front end to the database, you can experiment by writing a simple update statement against a view that you are trying to update through Access. if it succeeds, I am wrong and this is not the issue. But I suspect it will fail.
Since my original posting, I have become an admin in a "lab space". This database is setup the exact same way as our production db (meaning actually two Dbs, one for tables another for views to tables). And I have recreated the same problem, regardless of using the tables for the views to try to update. Running a UPDATE query in TSA is no problem, when Access doesn't work, and I need an update because an append query won't accomplish what I need, I use a TSA update query.
As far as the steps I go through, I just link a table, design a query to update a particular field without any criteria, and get the error message. Even when I link a table, and try to type into the grid, the message "This Recordset Is Not Updateable" appears in the lower left. Again, this is a database in which I am the administrator.
I think I may be having success with this. I'll certainly have to test more. But it appears that when linking the table, you *must* define a unique key (the last step in the link table process).