Operation must be an updatable query

[Burbonizer]

Ok i am getting to the point of screaming.

We have a database at work which has a button on the form that looks
at a tables written in foxpro called operations. This table has quite
a lot of data in it so we use a database to look at this data and add
certain information. It does not add it to the foxpro tables only
reads. It is purely a one way data viewing.

This button on the form used to go and look at the foxpro tables and
see what was changed and update the access table. I say used to as
that is what i mean. It used to work. Now all of a sudden with no
database modifications it has stopped working. We now get the dreaded
"operation must be a updateable query". We can't understand why. I
have taken a copy of both foxpro and access tables and brought them
home to see if i can get it working but no matter what i try i get the
same error.

The weird thing is that when you do a view on the query, it shows the
results no problem, it's only when it tries to update the data. The
access table has no primary key. I have tried adding a primary key and
making it indexed but it still wont work. I thought maybe i had the
query the wrong way round but that too did not work. I have tried
another query in the database that is also a update query and i get
the same error again but it used to work. Has something changed with
the Microsoft op sys via updates?

At work we have the folder and the whole shared drive set as everyone
for the access. When the database is used it creates a ldb file no
problem and that does not get deleted until the last person is logged
out. Everything else works in the database only update queries.

Please help as this is really annoying and now we have to write
another update query. We tried and yet again same error. I can't see
how this is security permissions as nothing has changed.

HELP
thanks
James

 

[Ken Snell MVP]

Post the SQL statement of the update query so that we can assist.

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

 

[Burbonizer]

Hope this helps

UPDATE DISTINCTROW s_shead INNER JOIN [Order Status table] ON
s_shead.son = [Order Status table].SON SET s_shead.ordstatus = [Order
Status table]![Oper_Status];

 

[Ken Snell MVP]

You cannot use DISTINCTROW in an update query.... that involves aggregation
of the data, therefore ACCESS has no idea of which unique record(s) were
used to get that aggregated data.

Try just this:

UPDATE s_shead INNER JOIN [Order Status table] ON
s_shead.son = [Order Status table].SON
SET s_shead.ordstatus = [Order Status table]![Oper_Status];

 

[Burbonizer]

You cannot use DISTINCTROW in an update query.... that involves aggregation
of the data, therefore ACCESS has no idea of which unique record(s) were
used to get that aggregated data.

Try just this:

UPDATE s_shead INNER JOIN [Order Status table] ON
s_shead.son = [Order Status table].SON
SET s_shead.ordstatus = [Order Status table]![Oper_Status];

--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/

Hope this helps

UPDATE DISTINCTROW s_shead INNER JOIN [Order Status table] ON
s_shead.son = [Order Status table].SON SET s_shead.ordstatus = [Order
Status table]![Oper_Status];- Hide quoted text -

- Show quoted text -

Thanks for your response.

Unfortunatly i am still getting the "Operation must use an updateable
query" error.

James

 

[Bob Barrows]

Thanks for your response.

Unfortunatly i am still getting the "Operation must use an updateable
query" error.

That's unfortunate. Do you need help with it? If so, post the sql of the
query that's raising that error.

 

[Ken Snell MVP]

Is "s_shead" a query or a table? If it's a query, post the SQL satement for
it.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

You cannot use DISTINCTROW in an update query.... that involves
aggregation
of the data, therefore ACCESS has no idea of which unique record(s) were
used to get that aggregated data.

Try just this:

UPDATE s_shead INNER JOIN [Order Status table] ON
s_shead.son = [Order Status table].SON
SET s_shead.ordstatus = [Order Status table]![Oper_Status];

--

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/

Hope this helps

UPDATE DISTINCTROW s_shead INNER JOIN [Order Status table] ON
s_shead.son = [Order Status table].SON SET s_shead.ordstatus = [Order
Status table]![Oper_Status];- Hide quoted text -

- Show quoted text -

Thanks for your response.

Unfortunatly i am still getting the "Operation must use an updateable
query" error.

James

 

[Burbonizer]

Is "s_shead" a query or a table? If it's a query, post the SQL satement for
it.

--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/

You cannot use DISTINCTROW in an update query.... that involves
aggregation
of the data, therefore ACCESS has no idea of which unique record(s) were
used to get that aggregated data.

Try just this:

UPDATE s_shead INNER JOIN [Order Status table] ON
s_shead.son = [Order Status table].SON
SET s_shead.ordstatus = [Order Status table]![Oper_Status];

--

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/

news:3265d0c9-70f7-4c16-92a2-ab480580ff95@m19g2000yqk.googlegroups.com....

Hope this helps
UPDATE DISTINCTROW s_shead INNER JOIN [Order Status table] ON
s_shead.son = [Order Status table].SON SET s_shead.ordstatus = [Order
Status table]![Oper_Status];- Hide quoted text -

- Show quoted text -

Thanks for your response.

Unfortunatly i am still getting the "Operation must use an updateable
query" error.

James

S_Head is a table that is linked via ODBC.

This table is not updateable from Access only read only.

James

 

[Ken Snell MVP]

Is "s_shead" a query or a table? If it's a query, post the SQL satement
for
it.

S_Head is a table that is linked via ODBC.

This table is not updateable from Access only read only.

James