Operation must use and updatable query

#11

I had from 1 to 3 records under each person.
to figure out the calculations based on how many records and the value
of each one I did a union query and a crosstab so I could transpose
the records.

1--1000
1--2000
etc

2--500
2--200
2--100
etc

1---1000---2000---
2---500---200---100
etc
these values are the dia and sys values in the switch statement

then I used a query to put tbldemo the information about each person
together with this data so I had 1 record to calc on.

then based on these values I did this in the query
LETTERS: Int(Switch(([Dia1]<90 And [sys1]<90),1,(([Dia2]<90 And
[sys2]<90) Or ([dia3]<90 And
[sys3]<90)),2,[MissedVisitmark]=1,3,(([dia1]>=95 Or [sys1]>=95)) And
([dia2]>=95 Or [sys2]>=95) And ([dia3]>=95 Or
[sys3]>=95),5,(([dia1]>=90 Or [sys1]>=90)) And ([dia2]>=90 Or
[sys2]>=90) Or ([dia3]>=90 Or [sys3]>=90),4))

so I could determine which letter they received.
that is QryLetter1.

So if I am reading your answers correctly the querys I used to
transpose the data makes any work I do from that step foreward un
updateable.

from the time the data was used in the union query or the crosstab
query it is not longer usable for any type of update?

I can not think of any way to do the calculations based on the data
besides this.

I thought about putting the letter calc back into tbldemo.
BUT as I said this would also be an update query and since its built
around this I can't build something like letternum in tbldemo based on
the value I receive since it will be another invalid query.

------------------------

On Mon, 2 Aug 2010 16:53:06 -0400, "Bob Barrows"
<reb01501 (AT) NOyahoo (DOT) SPAMcom> wrote:

Quote:

sparks wrote:
I am starting to see this in anything new we build.
Everything that is already built is working fine.

Is this some security update on the machines themselves.
Most people are running windows 7 but some are still on XP.

looked on the ms pages

Operation must use and updatable query

The most common reason is that the Internet Guest account
(IUSR_MACHINE), which is by default part of the "Everyone" group, does
not have Write permissions on the database file (.mdb). To fix this
problem, use the Security tab in Explorer to adjust the properties for
this file so that the Internet Guest account has the correct
permissions.

That is incomplete. All users require Modify permissions on the _folder_
containing the mdb file. That is because users need to create, modify
and delete the .ldb file that is used to control multi-user activity.

I'm not saying that is the cause of your problem. Are you using a .mdb
file as the backend for an ASP application? Is that why you cited that
solution? In ASP, identifying the user whose account is being used is
the key. If your site is set to allow Anonymous access, and you have not
set up an account for the IUSR account to impersonate, then the relevant
user account is indeed, the IUSR_Machine account. In addition, the
IWAM_Machine account could also be relevant if you are accessing the
database in application or server start or end events. If you are using
Integrated Security and Anonymous is turned off, then the account of the
user who requested the page is the relevant account.

As others have said, permissions is not the only cause of this error.
Certain queries can not be used for updates. GROUP BY is a prime
example. Read Allen's article as sited by the other Bob :-)
study http://allenbrowne.com/ser-61.html
titled Why is my query read-only?

#12

Hi,

there are several alternative methods to getting your answer.

One way is to put your letters calculations into a user defined
public function.

Another would be to create a temporary table containing the key to
each record you wish to update and the value, then create an update
query using the target table and this temporary table.

A third way would be to create a procedure that uses a pair of
recordsets, the first holding the query, the second your table to be
updated and use vb code to update the table recordset from the query
recordset.

Bob

sparks <sparks (AT) home (DOT) com> wrote in
news:m01g56l9elq1tiil35rc1mff06kd3je8sh (AT) 4ax (DOT) com:

Quote:

I had from 1 to 3 records under each person.
to figure out the calculations based on how many records and the
value of each one I did a union query and a crosstab so I could
transpose the records.

1--1000
1--2000
etc

2--500
2--200
2--100
etc

1---1000---2000---
2---500---200---100
etc
these values are the dia and sys values in the switch statement

then I used a query to put tbldemo the information about each
person together with this data so I had 1 record to calc on.

then based on these values I did this in the query
LETTERS: Int(Switch(([Dia1]<90 And [sys1]<90),1,(([Dia2]<90 And
[sys2]<90) Or ([dia3]<90 And
[sys3]<90)),2,[MissedVisitmark]=1,3,(([dia1]>=95 Or [sys1]>=95))
And ([dia2]>=95 Or [sys2]>=95) And ([dia3]>=95 Or
[sys3]>=95),5,(([dia1]>=90 Or [sys1]>=90)) And ([dia2]>=90 Or
[sys2]>=90) Or ([dia3]>=90 Or [sys3]>=90),4))

so I could determine which letter they received.
that is QryLetter1.

So if I am reading your answers correctly the querys I used to
transpose the data makes any work I do from that step foreward un
updateable.

from the time the data was used in the union query or the crosstab
query it is not longer usable for any type of update?

I can not think of any way to do the calculations based on the
data besides this.

I thought about putting the letter calc back into tbldemo.
BUT as I said this would also be an update query and since its
built around this I can't build something like letternum in
tbldemo based on the value I receive since it will be another
invalid query.

------------------------

On Mon, 2 Aug 2010 16:53:06 -0400, "Bob Barrows"
reb01501 (AT) NOyahoo (DOT) SPAMcom> wrote:

sparks wrote:
I am starting to see this in anything new we build.
Everything that is already built is working fine.

Is this some security update on the machines themselves.
Most people are running windows 7 but some are still on XP.

looked on the ms pages

Operation must use and updatable query

The most common reason is that the Internet Guest account
(IUSR_MACHINE), which is by default part of the "Everyone"
group, does not have Write permissions on the database file
(.mdb). To fix this problem, use the Security tab in Explorer to
adjust the properties for this file so that the Internet Guest
account has the correct permissions.

That is incomplete. All users require Modify permissions on the
_folder_ containing the mdb file. That is because users need to
create, modify and delete the .ldb file that is used to control
multi-user activity.

I'm not saying that is the cause of your problem. Are you using a
.mdb file as the backend for an ASP application? Is that why you
cited that solution? In ASP, identifying the user whose account is
being used is the key. If your site is set to allow Anonymous
access, and you have not set up an account for the IUSR account to
impersonate, then the relevant user account is indeed, the
IUSR_Machine account. In addition, the IWAM_Machine account could
also be relevant if you are accessing the database in application
or server start or end events. If you are using Integrated
Security and Anonymous is turned off, then the account of the user
who requested the page is the relevant account.

As others have said, permissions is not the only cause of this
error. Certain queries can not be used for updates. GROUP BY is a
prime example. Read Allen's article as sited by the other Bob :-)
study http://allenbrowne.com/ser-61.html
titled Why is my query read-only?

#13

Thanks big time to all of you for your help.
I was convinced I was going to do it no matter what.
YEP that no matter what was the killer LOL

I just make a table, printed out and updated on that and then deleted
it.

It works fine now

thanks again for all your help with this

On Tue, 03 Aug 2010 13:20:07 GMT, Bob Quintal <rquintal (AT) sPAmpatico (DOT) ca>
wrote:

Quote:

Hi,

there are several alternative methods to getting your answer.

One way is to put your letters calculations into a user defined
public function.

Another would be to create a temporary table containing the key to
each record you wish to update and the value, then create an update
query using the target table and this temporary table.

A third way would be to create a procedure that uses a pair of
recordsets, the first holding the query, the second your table to be
updated and use vb code to update the table recordset from the query
recordset.

Bob

sparks <sparks (AT) home (DOT) com> wrote in
news:m01g56l9elq1tiil35rc1mff06kd3je8sh (AT) 4ax (DOT) com:

I had from 1 to 3 records under each person.
to figure out the calculations based on how many records and the
value of each one I did a union query and a crosstab so I could
transpose the records.

1--1000
1--2000
etc

2--500
2--200
2--100
etc

1---1000---2000---
2---500---200---100
etc
these values are the dia and sys values in the switch statement

then I used a query to put tbldemo the information about each
person together with this data so I had 1 record to calc on.

then based on these values I did this in the query
LETTERS: Int(Switch(([Dia1]<90 And [sys1]<90),1,(([Dia2]<90 And
[sys2]<90) Or ([dia3]<90 And
[sys3]<90)),2,[MissedVisitmark]=1,3,(([dia1]>=95 Or [sys1]>=95))
And ([dia2]>=95 Or [sys2]>=95) And ([dia3]>=95 Or
[sys3]>=95),5,(([dia1]>=90 Or [sys1]>=90)) And ([dia2]>=90 Or
[sys2]>=90) Or ([dia3]>=90 Or [sys3]>=90),4))

so I could determine which letter they received.
that is QryLetter1.

So if I am reading your answers correctly the querys I used to
transpose the data makes any work I do from that step foreward un
updateable.

from the time the data was used in the union query or the crosstab
query it is not longer usable for any type of update?

I can not think of any way to do the calculations based on the
data besides this.

I thought about putting the letter calc back into tbldemo.
BUT as I said this would also be an update query and since its
built around this I can't build something like letternum in
tbldemo based on the value I receive since it will be another
invalid query.

------------------------

On Mon, 2 Aug 2010 16:53:06 -0400, "Bob Barrows"
reb01501 (AT) NOyahoo (DOT) SPAMcom> wrote:

sparks wrote:
I am starting to see this in anything new we build.
Everything that is already built is working fine.

Is this some security update on the machines themselves.
Most people are running windows 7 but some are still on XP.

looked on the ms pages

Operation must use and updatable query

The most common reason is that the Internet Guest account
(IUSR_MACHINE), which is by default part of the "Everyone"
group, does not have Write permissions on the database file
(.mdb). To fix this problem, use the Security tab in Explorer to
adjust the properties for this file so that the Internet Guest
account has the correct permissions.

That is incomplete. All users require Modify permissions on the
_folder_ containing the mdb file. That is because users need to
create, modify and delete the .ldb file that is used to control
multi-user activity.

I'm not saying that is the cause of your problem. Are you using a
.mdb file as the backend for an ASP application? Is that why you
cited that solution? In ASP, identifying the user whose account is
being used is the key. If your site is set to allow Anonymous
access, and you have not set up an account for the IUSR account to
impersonate, then the relevant user account is indeed, the
IUSR_Machine account. In addition, the IWAM_Machine account could
also be relevant if you are accessing the database in application
or server start or end events. If you are using Integrated
Security and Anonymous is turned off, then the account of the user
who requested the page is the relevant account.

As others have said, permissions is not the only cause of this
error. Certain queries can not be used for updates. GROUP BY is a
prime example. Read Allen's article as sited by the other Bob :-)
study http://allenbrowne.com/ser-61.html
titled Why is my query read-only?

#14

bob;

you could do that with an 'instead of trigger' using SQL Server
right?

why do you limit yourself to an obsolete database, script kid?

-Aaron

On Aug 2, 8:07*am, Bob Quintal <rquin... (AT) sPAmpatico (DOT) ca> wrote:

Quote:

The query below is most definitely NOT UPDATEABLE.
It contains a Group By Clause. No query with a Group By clause is
updateable.

Bob

sparks <spa... (AT) home (DOT) com> wrote innews:25ld569u61uh9su5j071al3gi11ovn6t6s (AT) 4ax (DOT) com:

SELECT Query32.PersonID, Query100.rowhead, Query32.FirstName,
Query32.LastName, Query32.ParentName, Query32.ParentAddress,
Query32.School, Query32.MissedVisitmark, Query100.Dia1,
Query100.Sys1, Query100.Dia2, Query100.Sys2, Query100.Dia3,
Query100.Sys3, Int(Switch(([Dia1]<90 And [sys1]<90),1,(([Dia2]<90
And [sys2]<90) Or ([dia3]<90 And
[sys3]<90)),2,[MissedVisitmark]=1,3,(([dia1]>=95 Or [sys1]>=95))
And ([dia2]>=95 Or [sys2]>=95) And ([dia3]>=95 Or
[sys3]>=95),5,(([dia1]>=90 Or [sys1]>=90)) And ([dia2]>=90 Or
[sys2]>=90) Or ([dia3]>=90 Or [sys3]>=90),4)) AS LETTERS,
Query32.ExportCode, Query32.ExportFile FROM Query32 RIGHT JOIN
Query100 ON Query32.AUTOID = Query100.rowhead GROUP BY
Query32.ChildsID, Query100.rowhead, Query32.FirstName,
Query32.LastName, Query32.ParentName, Query32.ParentAddress,
Query32.School, Query32.MissedVisitmark, Query100.Dia1,
Query100.Sys1, Query100.Dia2, Query100.Sys2, Query100.Dia3,
Query100.Sys3, Int(Switch(([Dia1]<90 And [sys1]<90),1,(([Dia2]<90
And [sys2]<90) Or ([dia3]<90 And
[sys3]<90)),2,[MissedVisitmark]=1,3,(([dia1]>=95 Or [sys1]>=95))
And ([dia2]>=95 Or [sys2]>=95) And ([dia3]>=95 Or
[sys3]>=95),5,(([dia1]>=90 Or [sys1]>=90)) And ([dia2]>=90 Or
[sys2]>=90) Or ([dia3]>=90 Or [sys3]>=90),4)), Query32.ExportCode,
Query32.ExportFile
HAVING (((Int(Switch(([Dia1]<90 And [sys1]<90),1,(([Dia2]<90 And
[sys2]<90) Or ([dia3]<90 And
[sys3]<90)),2,[MissedVisitmark]=1,3,(([dia1]>=95 Or [sys1]>=95))
And ([dia2]>=95 Or [sys2]>=95) And ([dia3]>=95 Or
[sys3]>=95),5,(([dia1]>=90 Or [sys1]>=90)) And ([dia2]>=90 Or
[sys2]>=90) Or ([dia3]>=90 Or [sys3]>=90),4)))=1))
ORDER BY Query100.rowhead;

I did not use any wild cards.

I do not understand why I can do this type of query in other
(previous databases) and all is fine.
I just did this database this month.
I am using a mail merge and updating the exportcode to show that
it has been printed.

I am updating the table *tbldemo based on matches listed in the
query.
*SET tbldemo.ExportCode = 1, tbldemo.exportfile=""" &
*Letterstring & """;")

I have done this in the past and don't see what updatable query
has to do with it.

What threw me at first was I thought it said update query
But its updateable query.

On Mon, 2 Aug 2010 10:06:27 -0400, "Mark Andrews"
mandrews___NOSPAM... (AT) rptsoftware (DOT) com> wrote:

I know I have seen a few weird things when a query is in the mix
of the join and it uses a .* to show all fields.

Otherwise I'm sure other could help more if you supply the sql for
qryletter1

Mark

"sparks" <spa... (AT) home (DOT) com> wrote in message
news:cbgd56trqc89goef5t92db8cok7h6ld6p7 (AT) 4ax (DOT) com...

* * * *Call MergeAllWord(Letterstring, "qryletter1", "Normal",
strDir:=DirToPath("word\", False))

this is the query

* * * * * * * *DoCmd.RunSQL ("UPDATE qryletter1" & " INNER JOIN
tbldemo ON qryletter1" & ".PersonID = tbldemo.PersonID" & _
* * * * * * * *" SET tbldemo.ExportCode = 1,
* * * * * * * *tbldemo.exportfile=""" &
Letterstring & """;")

it is supposed to find the exports in qryletter1 that are
missing and print the letters with missing exportcode, and it
does. but when it hits this command to update tbldemo I get

Operation Must use and Updateable Query

On Mon, 02 Aug 2010 06:59:44 -0500, sparks <spa... (AT) home (DOT) com
wrote:

I am starting to see this in anything new we build.
Everything that is already built is working fine.

Is this some security update on the machines themselves.
Most people are running windows 7 but some are still on XP.

looked on the ms pages

Operation must use and updatable query

The most common reason is that the Internet Guest account
(IUSR_MACHINE), which is by default part of the "Everyone"
group, does not have Write permissions on the database file
(.mdb). To fix this problem, use the Security tab in Explorer to
adjust the properties for this file so that the Internet Guest
account has the correct permissions.

A second cause of this error is that the database was not opened
with the correct MODE for writing.

Anyone know what to look for to determine problems on new files
that work fine on old files.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Operation must use and updatable query

comp.databases.ms-access comp.databases.ms-access

Re: Operation must use and updatable query - 08-03-2010 , 07:08 AM

Re: Operation must use and updatable query - 08-03-2010 , 08:20 AM

Re: Operation must use and updatable query - 08-04-2010 , 07:54 AM

Re: Operation must use and updatable query - 08-17-2010 , 02:22 AM