If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Operation Must Use An Updateable Query!? - NOT Permissions - CrossTab!!??
running access 2k; mdb w/ linked tables to another mdb
(front/back-end); trying to run a query that updates a table FROM information from a crosstab query. I AM NOTT trying to update the crosstab query itself - only use information FROM it to update a perfectly updatable table! Why the ---- would I get this error, when there are clearly NO permission issues, or issues in updating the table I'm trying to update!?!?!?!? (I can clearly update it without the XT) What the ---- good is a crosstab query, if I can't USE the information within it for something other than reporting?!?!? Here's my query (the "XT" table is my crosstab): I'm guessing that maybe I need to use a sub-query to work around this, but am not sure how, nor why I should have to go this route.... UPDATE Q_answers_XT LEFT JOIN T_final_answers ON Q_answers_XT.id = T_final_answers.id SET T_final_answers.q = [Q_answers_XT]![q], T_final_answers.a = [Q_answers_XT]![a]; any help appreciated... TIA |
#2
|
|||
|
|||
Operation Must Use An Updateable Query!? - NOT Permissions - CrossTab!!??
By design you can't use a crosstab or any aggregate query in an update query
(except as a subquery in the where clause). You may have to resort to using the cross-tab to create (or populate) a temporary work table. Then you can use the work table in a query to update your fields in T_Final_Answers. You might be able to use the VBA function - DLookup - to find the value in the crosstab and use that in your update query. Something like the following UNTESTED query. UPDATE T_Final_Answers SET q = DLookup("q","q_answers_xt","id=" & T_final_answers.Id) WHERE ID in (SELECT ID FROM q_answers_Xt) "Bob" wrote in message oups.com... running access 2k; mdb w/ linked tables to another mdb (front/back-end); trying to run a query that updates a table FROM information from a crosstab query. I AM NOTT trying to update the crosstab query itself - only use information FROM it to update a perfectly updatable table! Why the ---- would I get this error, when there are clearly NO permission issues, or issues in updating the table I'm trying to update!?!?!?!? (I can clearly update it without the XT) What the ---- good is a crosstab query, if I can't USE the information within it for something other than reporting?!?!? Here's my query (the "XT" table is my crosstab): I'm guessing that maybe I need to use a sub-query to work around this, but am not sure how, nor why I should have to go this route.... UPDATE Q_answers_XT LEFT JOIN T_final_answers ON Q_answers_XT.id = T_final_answers.id SET T_final_answers.q = [Q_answers_XT]![q], T_final_answers.a = [Q_answers_XT]![a]; any help appreciated... TIA |
#3
|
|||
|
|||
Operation Must Use An Updateable Query!? - NOT Permissions - CrossTab!!??
Tom, & John;
I'm very appreciative for your replies. John; I'll try your suggestion, and see how it goes - I REALLY don't want to have to create a temp table everytime this needs to happen - just becomes a compaction nightmare. This is about as brain-dead, as anything microsoft has ever done; and they've sure done plenty. Can't tell you how many times their idiosy has ticked me off. When the ---- are people going to STOP paying microsoft for BS marketing improvements, and force them to write good code? (that is retorical, of course, because I know this will never happen - other companies follow microsoft's example because people are stupid, or ignorant enough to let it happen). ok, ok - close the vents, I'm done blowing steam into the empty vacuum of microsoft customer awareness...... :-) T & J - again, I'm very appreciative for your suggestions; clearly, if John's suggestion fails, I have no choice but to create a temporary table - again & again & again & again, and compact again & again & again .....;-O TX - Bob John Spencer wrote: By design you can't use a crosstab or any aggregate query in an update query (except as a subquery in the where clause). You may have to resort to using the cross-tab to create (or populate) a temporary work table. Then you can use the work table in a query to update your fields in T_Final_Answers. You might be able to use the VBA function - DLookup - to find the value in the crosstab and use that in your update query. Something like the following UNTESTED query. UPDATE T_Final_Answers SET q = DLookup("q","q_answers_xt","id=" & T_final_answers.Id) WHERE ID in (SELECT ID FROM q_answers_Xt) "Bob" wrote in message oups.com... running access 2k; mdb w/ linked tables to another mdb (front/back-end); trying to run a query that updates a table FROM information from a crosstab query. I AM NOTT trying to update the crosstab query itself - only use information FROM it to update a perfectly updatable table! Why the ---- would I get this error, when there are clearly NO permission issues, or issues in updating the table I'm trying to update!?!?!?!? (I can clearly update it without the XT) What the ---- good is a crosstab query, if I can't USE the information within it for something other than reporting?!?!? Here's my query (the "XT" table is my crosstab): I'm guessing that maybe I need to use a sub-query to work around this, but am not sure how, nor why I should have to go this route.... UPDATE Q_answers_XT LEFT JOIN T_final_answers ON Q_answers_XT.id = T_final_answers.id SET T_final_answers.q = [Q_answers_XT]![q], T_final_answers.a = [Q_answers_XT]![a]; any help appreciated... TIA |
#4
|
|||
|
|||
Operation Must Use An Updateable Query!? - NOT Permissions - CrossTab!!??
Don't forget that you can also use a pair of recordsets and use one for the
crosstab and one for the update. |
#5
|
|||
|
|||
Operation Must Use An Updateable Query!? - NOT Permissions - CrossTab!!??
BTW, John;
Just wanted to let you know, that your suggestion did work... Although dlookup is about as cycle intensive as it gets, I think it's still better than constantly re-creating, and compacting huge tables... TX again! - Bob John Spencer wrote: By design you can't use a crosstab or any aggregate query in an update query (except as a subquery in the where clause). You may have to resort to using the cross-tab to create (or populate) a temporary work table. Then you can use the work table in a query to update your fields in T_Final_Answers. You might be able to use the VBA function - DLookup - to find the value in the crosstab and use that in your update query. Something like the following UNTESTED query. UPDATE T_Final_Answers SET q = DLookup("q","q_answers_xt","id=" & T_final_answers.Id) WHERE ID in (SELECT ID FROM q_answers_Xt) "Bob" wrote in message oups.com... running access 2k; mdb w/ linked tables to another mdb (front/back-end); trying to run a query that updates a table FROM information from a crosstab query. I AM NOTT trying to update the crosstab query itself - only use information FROM it to update a perfectly updatable table! Why the ---- would I get this error, when there are clearly NO permission issues, or issues in updating the table I'm trying to update!?!?!?!? (I can clearly update it without the XT) What the ---- good is a crosstab query, if I can't USE the information within it for something other than reporting?!?!? Here's my query (the "XT" table is my crosstab): I'm guessing that maybe I need to use a sub-query to work around this, but am not sure how, nor why I should have to go this route.... UPDATE Q_answers_XT LEFT JOIN T_final_answers ON Q_answers_XT.id = T_final_answers.id SET T_final_answers.q = [Q_answers_XT]![q], T_final_answers.a = [Q_answers_XT]![a]; any help appreciated... TIA |
#6
|
|||
|
|||
Operation Must Use An Updateable Query!? - NOT Permissions - CrossTab!!??
Rick;
TX also, for your reply - when you talk about a pair of recordsets, you don't mean a sub-query - right? Could you ellaborate / illustrate please? TIA! - Bob Rick Wannall wrote: Don't forget that you can also use a pair of recordsets and use one for the crosstab and one for the update. |
#7
|
|||
|
|||
Operation Must Use An Updateable Query!? - NOT Permissions - CrossTab!!??
Sure. This example assumes that you want to open the xtab and then go find
rows in the other recordset (query) to update. Reversing this is no real challenge. dim strWhere as string dim rXT as ado.recordset dim rUpdate as ado.recordset .... (get connection(s) ... set rXT = new ado.recordset set rUpdate = new ado.recordset rXT.open "Select xt.* From MyCrosstabQuery As xt" , myconnection1... if rXT .bof=true and rXT.eof = true endif 'put cleanup code here and exit. There are no records in the xtab query. endif rXT.MoveFirst do until rxt.eof = true strWhere = "criterionfield1 = " & rXT.Fields("xtabcriterionfield1") rUpdate.Open "Select updatefield1, updatefield2,... from MyOtherQuery Where " & strWhere, myconnection2 if rUPdate.bof = true and rupdate.eof = true then 'close rupdate and continue to the next row of xtab rupdate.close else rupdate.movefirst do until rupdate.eof = true rupdate.fields("updatefield1") = rxt.fields("xtabvaluefield1") rupdate.fields("updatefield2") = rxt.fields("xtabvaluefield2") rupdate.update rupdate.movenext loop endif rxt.MoveNext loop rxt.close set rxt = nothing set rupdate = nothing set myconnection1 = nothing set myconnection2=nothing This is of course pseudocode, not based on an actual table, but it should give you the structure you need for the task. criterionfield1 is the name of a field in your table to update, a field you would use to locate rows to update. xtabcriterionfield1 would be the field in your crosstab query that you would use to locate a row in the table to update. updatefield and xtabvalue field are respectively the destination and source fields for the udpate. |
#8
|
|||
|
|||
Operation Must Use An Updateable Query!? - NOT Permissions - CrossTab!!??
Rick-
I understand now, and am ever-appreciative for your time!! TX again - Bob Rick Wannall wrote: Sure. This example assumes that you want to open the xtab and then go find rows in the other recordset (query) to update. Reversing this is no real challenge. dim strWhere as string dim rXT as ado.recordset dim rUpdate as ado.recordset ... (get connection(s) ... set rXT = new ado.recordset set rUpdate = new ado.recordset rXT.open "Select xt.* From MyCrosstabQuery As xt" , myconnection1... if rXT .bof=true and rXT.eof = true endif 'put cleanup code here and exit. There are no records in the xtab query. endif rXT.MoveFirst do until rxt.eof = true strWhere = "criterionfield1 = " & rXT.Fields("xtabcriterionfield1") rUpdate.Open "Select updatefield1, updatefield2,... from MyOtherQuery Where " & strWhere, myconnection2 if rUPdate.bof = true and rupdate.eof = true then 'close rupdate and continue to the next row of xtab rupdate.close else rupdate.movefirst do until rupdate.eof = true rupdate.fields("updatefield1") = rxt.fields("xtabvaluefield1") rupdate.fields("updatefield2") = rxt.fields("xtabvaluefield2") rupdate.update rupdate.movenext loop endif rxt.MoveNext loop rxt.close set rxt = nothing set rupdate = nothing set myconnection1 = nothing set myconnection2=nothing This is of course pseudocode, not based on an actual table, but it should give you the structure you need for the task. criterionfield1 is the name of a field in your table to update, a field you would use to locate rows to update. xtabcriterionfield1 would be the field in your crosstab query that you would use to locate a row in the table to update. updatefield and xtabvalue field are respectively the destination and source fields for the udpate. |
#9
|
|||
|
|||
Operation Must Use An Updateable Query!? - NOT Permissions - CrossTab!!??
"Bob" wrote in
ups.com: This is about as brain-dead, as anything microsoft has ever done; and they've sure done plenty. Can't tell you how many times their idiosy has ticked me off. Well, pre-Access 97, a lot of these things worked, but were completely inconsistent with every other SQL dialect. With A97/Jet 3.5, Microsoft tightened up the join and updatability rules to be more consistent with other flavors of SQL, which is probably something that was required in the push to make Access interoperable with SQL Server. Then there's SQL 92 support in A2K and later. I doubt it would solve the problem easily, but it might provide other solutions to the problem of unupdatability. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#10
|
|||
|
|||
Operation Must Use An Updateable Query!? - NOT Permissions - CrossTab!!??
"Bob" wrote in
oups.com: Although dlookup is about as cycle intensive as it gets, I think it's still better than constantly re-creating, and compacting huge tables... It's not. Temp tables are going to be *much* faster. If it's a regular operation, just keep a copy of the empty temp database and copy over it when your app exits. That way you have a clean copy of the temp database every time you start your app, and never have to compact it. Others say it's quick to recreate the temp table in code, once the code is written. Me, I've never taken the time to write such code, so find it easier to run a MakeTable query, then edit the resulting table to have appropriate indexing, then empty it out, copy it into the temp database and compact it. I was surprised to discover that the time it takes to write to the temp table is not greater than the alternatives that use all memory. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Query (or Report) Generation Problem | Paputxi | Running & Setting Up Queries | 9 | May 30th, 2006 07:52 PM |
Operation must use an updateable query. | Viswanathan S | General Discussion | 1 | August 27th, 2005 10:27 PM |
"Operation must use an updateable query" On UPDATE Only | MDW | Running & Setting Up Queries | 1 | May 10th, 2005 07:30 PM |
None of your e-mail accounts could send to this recipient. | shayefairy | General Discussion | 6 | February 17th, 2005 03:11 PM |
Operation must use updateable query | Brian | Running & Setting Up Queries | 1 | January 1st, 2005 01:46 AM |