SQL Update woes in MS Access – Operation must use an updateable query

by

I have to weigh in with David W. Fenton’s comment on the OP.

This is highly annoying problem with Jet/ACE. But try either:

  1. go to the query properties (click
    the background of the pane where the
    tables are displayed) and set
    ‘Unique Records’ to ‘Yes’
  2. Option 1 is the equivalent of adding
    the somewhat strange looking
    DISTINCTROW keyword to the
    SELECT clause, eg

:

UPDATE DISTINCTROW tblClient 
       INNER JOIN qryICMSClientCMFinite 
          ON tblClient.ClientID = qryICMSClientCMFinite.ClientID
   SET tblClient.ClientCMType = "F";

This solves so many problems involving this error message that it is almost ridiculous.

That’s MS Access in a nutshell – if you don’t know the trade-secret workaround for problem x, you can take days trying to find the answer. To know the 10,000 workarounds IS to program Access. Is that enough of a warning for the uninitiated ?

Ben

More Related Contents:

  1. PL SQL UPDATE STATEMENT
  2. How can I do an UPDATE statement with JOIN in SQL Server?
  3. SQL multiple join statement
  4. Oracle SQL: Update a table with data from another table
  5. What’s the difference between DoCmd.SetWarnings and CurrentDB.Execute
  6. How to do INSERT into a table records extracted from another table
  7. Multiple INNER JOIN SQL ACCESS
  8. updating table rows in postgres using subquery
  9. is there a group_concat function in ms-access?
  10. how can I Update top 100 records in sql server
  11. Return pre-UPDATE column values using SQL only
  12. SQL UPDATE order of evaluation
  13. [] brackets in sql statements
  14. SQL Server Update Trigger, Get Only modified fields
  15. Use SELECT inside an UPDATE query
  16. Row numbers in query result using Microsoft Access
  17. SQL to add column with default value – Access 2003
  18. Access – compare two tables and update or insert data in first table
  19. “You tried to execute a query that does not include the specified aggregate function”
  20. I want to use CASE statement to update some records in sql server 2005
  21. Bulk/batch update/upsert in PostgreSQL
  22. How can I implement SQL INTERSECT and MINUS operations in MS Access
  23. Update a column of a table with a column of another table in PostgreSQL
  24. Functions with variable number of input parameters
  25. Update query using Subquery in Sql Server
  26. Update rows in one table with data from another table based on one column in each being equal
  27. How do I perform update query with subquery in Access?
  28. How do I create a decimal field in Access with Alter Table?
  29. Update a table with data from another table
  30. Escaping ‘ in Access SQL

Leave a Comment