operation must use an updatable query

  • Thread starter Thread starter iccsi
  • Start date Start date
I

iccsi

I have an update query and I got following message when I run it

"operation must use an updatable query"

The update query has one table and one query and I update the table.
Can you please advise where I have look for the message?

Your help is great appreciated,
 
I have an update query and I got following message when I run it

"operation must use an updatable query"

The update query has one table and one query and I update the table.
Can you please advise where I have look for the message?

Your help is great appreciated,
Click to expand...

Please open the query in SQL view and post the SQL text here. There are quite
a few reasons why a query might not be updateable; one common one is that any
query which has a GROUP BY, or any query built on a GROUP BY query, is not
updateable.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
The most common problem users in my user group have found is that they did
not include the key field from the table they wanted to update. If you join
the table and query on a key field, but the field you retrieve and display
is from the query, you may not be able to update the table (even though the
values are identical).

But, as John says, there are quite a number of reasons. I found Help to be
good on explaining why queries may be unupdateable (in some, but not all,
versions of Access -- Help is one of the great variables between versions).

Larry Linson
Microsoft Office Access MVP
 
General information about updatable / nonupdatable queries:

An updatable query allows you to edit existing data or to add new records or
to delete existing records when the query is open in datasheet view. A
nonupdatable query will not let you do any of these things, and if you build
a form based on such a query the form also will not let you do such things.

See these articles for information about what can make a query nonupdatable
(watch for line-wrapping):

When can I update data from a query?
http://msdn.microsoft.com/library/d...l/acconDeterminingWhenCanUpdateDataQueryS.asp
http://msdn2.microsoft.com/en-us/library/aa198446(office.10).aspx

Harnessing the Power of Updatable Queries
http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/dnaraccgen/html/msdn_harness.asp

ACC2000: Cannot Edit or Update Record on a Form
http://support.microsoft.com/default.aspx?scid=kb;en-us;209571&Product=acc

ACC2000: Status Bar Displays "Recordset Not Updateable" Message When You Try
to Update a Linked Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;304179&Product=acc

INFO: Troubleshooting Errors That May Occur When You Update Data in Queries
and in Forms
http://support.microsoft.com/default.aspx?scid=kb;en-us;328828&Product=acc

ACC: Update Query Based on Totals Query Fails
http://support.microsoft.com/default.aspx?scid=kb;en-us;116142&Product=acc


Additionally, Allen Browne (MVP) has information about updatable and
nonupdatable queries:
http://allenbrowne.com/ser-61.html
 
Back
Top