Operation Must Use An Updateable Query!? - NOT Permissions - CrossTab!!??

[Bob]

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

 

[John Spencer]

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]

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

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)

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

 

[Rick Wannall]

Don't forget that you can also use a pair of recordsets and use one for the
crosstab and one for the update.

 

[Bob]

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

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)

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

 

[Bob]

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]

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.

 

[Bob]

Rick-

I understand now, and am ever-appreciative for your time!!

TX again - Bob

 

[David W. Fenton]

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]

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.

 

[dbahooker]

don't use MDB for anything.

use SQL Server.

you can display data in whatever format you want; and you don't have to
worry about random problems like 'operation must use an updatable
query'

MDB is just too flaky for anything; and anyone that uses it anywhere
for anything should be shot.

-Aaron

 

[Bob]

David;

many TX for your reply - I was unaware of an SQL support updates for
access...
I'll have to check that out - even though I doubt it'll fix this issue,
as you say, it may help elseware....

Bob

 

[Bob]

TX again, David;

I agree with your suggestion; and taken with someone else's idea to
have the temp table in a totally separate db, resolves all my
compacting concerns....

Bob

 

[Bob]

Aaron-

tx for your input; I happen to agree with you, to a great degree. I do
believe that mdb's have their place; but in my situation, I'm dealing
with legacy software, and upgrading to SQL just hasn't been a priority.

Although, if I took the time I've wasted trying to make my
crosstab-update work, and used it to migrate..... )

MDB's are the preferred solution when dealing with end user's that want
to get their fingers a little dirty; also easy to support. I have alot
of users that do many varying ad-hoc queries, and it's much better to
let them do it all within an mdb.

Bob

don't use MDB for anything.

use SQL Server.

you can display data in whatever format you want; and you don't have to
worry about random problems like 'operation must use an updatable
query'

MDB is just too flaky for anything; and anyone that uses it anywhere
for anything should be shot.

-Aaron

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

 

[Rick Wannall]

Users who want to get their fingers a little dirty can do so in an MDE, if
that means creating queries, which is about all users should be turned loose
to do. Even then, you have the issue of preserving queries created by users
if you replace the MDE with a new release.

Do your users want to create forms and reports?

 

[David W. Fenton]

(e-mail address removed) wrote in

don't use MDB for anything.

use SQL Server.

you can display data in whatever format you want; and you don't
have to worry about random problems like 'operation must use an
updatable query'

MDB is just too flaky for anything; and anyone that uses it
anywhere for anything should be shot.

This is a completely idiotic post, which reflects no actual
knowledge or understanding of how to use and maintain Jet databases.

 

[Bob]

Rick-

they do create reports, and rarely forms; also this isn't mission
critical data, so I don't need the protection / restrictions of the mde
- although I didn't know that you could create/save queries in an mde -
I thought it didn't let you do anything [of a development nature]....

Bob

 

[Bob]

(LOL)

David - I'll grant you that - I was trying to be nice

although, I will partially defend dbahooker (certainly not the way he
communicates it), in that on SEVERAL occasions, I have had an mdb
database go corrupt on me in some very strange ways - beyond the point
of repair (in fact, I couldn't even import my work into a clean
database), and I lost DAYS of work in 1 such case. Whenever I work with
mdb's I ALWAYS make a new copy of the database before I open it because
of this experience.

I have NEVER had such an experience with SQL. Granted, that access can
both be the front end AND back end; and SQL only serves as a back-end;
so my comparison isn't 100% pure..... but you get the idea....

Bob

 

[David W. Fenton]

although, I will partially defend dbahooker (certainly not the
way he communicates it), in that on SEVERAL occasions, I have had
an mdb database go corrupt on me in some very strange ways -
beyond the point of repair (in fact, I couldn't even import my
work into a clean database), and I lost DAYS of work in 1 such
case. Whenever I work with mdb's I ALWAYS make a new copy of the
database before I open it because of this experience.

I have never seen an MDB corrupted beyond the ability of Peter
Miller to retrieve it. Of course, I've only once had an MDB that *I*
couldn't recover, so that's not many times.

If you encounter corruption on a regular basis, then I'd say you're
doing something wrong.

 

[Bob]

I wouldn't say it happens on a regular basis, considering how often I
work in the environment, it's very rare. But it only takes 1 bad
experience to sour / caution you forever-more ....

Only that 1 time was I unable to repair/recover it; and it that 1 case,
PM wouldn't have been able to either.... it was VERY strange. Not even
sure if I didn't have a momentary [hardware] issue with memory....