Operation must use and updatable query
in [Help]
|
From: sparks on 3 Aug 2010 08:08 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(a)NOyahoo.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?
From: Bob Quintal on 3 Aug 2010 09:20 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(a)home.com> wrote in news:m01g56l9elq1tiil35rc1mff06kd3je8sh(a)4ax.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(a)NOyahoo.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? >
From: sparks on 4 Aug 2010 08:54 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(a)sPAmpatico.ca> wrote: >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(a)home.com> wrote in >news:m01g56l9elq1tiil35rc1mff06kd3je8sh(a)4ax.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(a)NOyahoo.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? >>
First
|
Prev
|
Pages: 1 2 3 Prev: Help! I can't see the data in my tables (A2010) Next: THANKS GOD! I GOT $2000 FROM PAYPAL.... |