By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,484 Members | 1,811 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,484 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

May 31 '06 #1
Share this Question
Share on Google+
27 Replies


P: n/a
On 31 May 2006 06:55:25 -0700, "Bob" <bo*************@gmail.com>
wrote:

Yes, that's very annoying. Access apparently is too stupid to
understand this. We'll see about Access 12, but my guess is that 98%
of the effort went into a new UI, and very little into improvements
like this one. My workaround is to write the results of the crosstab
to a "temporary" table, and then use that table to complete the rest
of the update.

-Tom.

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


May 31 '06 #2

P: n/a
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" <bo*************@gmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr 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

May 31 '06 #3

P: n/a
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

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" <bo*************@gmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr 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


May 31 '06 #4

P: n/a
On 31 May 2006 09:52:12 -0700, "Bob" <bo*************@gmail.com>
wrote:

If DB bloat is a serious concern, you could create the "temp" table in
a separate MDB you create on the fly, use for your query, and delete.

Or upsize to SQL Server Express Edition?

-Tom.

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" <bo*************@gmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr 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
>


May 31 '06 #5

P: n/a
Don't forget that you can also use a pair of recordsets and use one for the
crosstab and one for the update.
May 31 '06 #6

P: n/a
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

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" <bo*************@gmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr 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


May 31 '06 #7

P: n/a
Bob
Tom-

That's an excellent (and obvious - don't know why I didn't think of it)
suggestion!

TX! - Bob

Tom van Stiphout wrote:
On 31 May 2006 09:52:12 -0700, "Bob" <bo*************@gmail.com>
wrote:

If DB bloat is a serious concern, you could create the "temp" table in
a separate MDB you create on the fly, use for your query, and delete.

Or upsize to SQL Server Express Edition?

-Tom.

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" <bo*************@gmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr 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
>


May 31 '06 #8

P: n/a
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 wrote:
Don't forget that you can also use a pair of recordsets and use one for the
crosstab and one for the update.


May 31 '06 #9

P: n/a
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.
May 31 '06 #10

P: n/a
Bob
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.


May 31 '06 #11

P: n/a
"Bob" <bo*************@gmail.com> wrote in
news:11**********************@f6g2000cwb.googlegro 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/
May 31 '06 #12

P: n/a
"Bob" <bo*************@gmail.com> wrote in
news:11**********************@j55g2000cwa.googlegr 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/
May 31 '06 #13

P: n/a
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 wrote:
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


May 31 '06 #14

P: n/a
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

David W. Fenton wrote:
"Bob" <bo*************@gmail.com> wrote in
news:11**********************@f6g2000cwb.googlegro 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/


Jun 1 '06 #15

P: n/a
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

David W. Fenton wrote:
"Bob" <bo*************@gmail.com> wrote in
news:11**********************@j55g2000cwa.googlegr 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/


Jun 1 '06 #16

P: n/a
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..... :o)

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

db*******@hotmail.com wrote:
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 wrote:
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


Jun 1 '06 #17

P: n/a
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?
Jun 1 '06 #18

P: n/a
db*******@hotmail.com wrote in
news:11**********************@c74g2000cwc.googlegr oups.com:
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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jun 1 '06 #19

P: n/a
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

Rick Wannall wrote:
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?


Jun 1 '06 #20

P: n/a
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 wrote:
db*******@hotmail.com wrote in
news:11**********************@c74g2000cwc.googlegr oups.com:
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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/


Jun 1 '06 #21

P: n/a
"Bob" <bo*************@gmail.com> wrote in
news:11*********************@h76g2000cwa.googlegro ups.com:
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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jun 2 '06 #22

P: n/a
On 1 Jun 2006 15:35:24 -0700, "Bob" <bo*************@gmail.com> wrote:

If I had adventurous users like that, I would tell them: create your
own MDB, and attach to the backend on the server, and create all you
want. I would not encourage them to use the production frontend for
it, especially because I want to reserve the right to replace it with
a new version any time I see fit.

-Tom.

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

Rick Wannall wrote:
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?


Jun 2 '06 #23

P: n/a
Amen.
Jun 2 '06 #24

P: n/a
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....

David W. Fenton wrote:
"Bob" <bo*************@gmail.com> wrote in
news:11*********************@h76g2000cwa.googlegro ups.com:
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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/


Jun 2 '06 #25

P: n/a
Bob
I'm right there with you, Tom;

These people only get to play with an mdb COPY of data exported from
the REAL db.

I'M not that adventurous (or trusting) .... even if the data isn't
mission crit... ;-O

Bob

Tom van Stiphout wrote:
On 1 Jun 2006 15:35:24 -0700, "Bob" <bo*************@gmail.com> wrote:

If I had adventurous users like that, I would tell them: create your
own MDB, and attach to the backend on the server, and create all you
want. I would not encourage them to use the production frontend for
it, especially because I want to reserve the right to replace it with
a new version any time I see fit.

-Tom.

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

Rick Wannall wrote:
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?


Jun 2 '06 #26

P: n/a
"Bob" <bo*************@gmail.com> wrote in
news:11**********************@y43g2000cwc.googlegr oups.com:
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....


How do you know he couldn't? Do you have the tools to explore the
internal Jet file structure and the knowledge of it to be able to
say that data was not recoverable? Peter does have those tools and
that knowledge, having developed them himself over the years. I
would never presume to guess whether or not a corrupted MDB is
recoverable or not, given that I have no evidence or knowledge that
gives me a chance of making any kind of judgment of just how corrupt
it is internally.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jun 2 '06 #27

P: n/a
Bob
David;

Of course, you are correct; I cannot say that definitively. Though I
can say it with a great deal of confidence. I examinded it with a hex
editor, and found huge area's of severe corruption. It was as though
the "framework" was still in place, but the innerd's with all the
important stuff was gone. I would analogize it with having a hard disk
with partition table, and directory structure all intact, and you could
view the directory; but None of the files had any data what-so-ever (if
you've ever done intimate work with fat32, you know what I mean).

In any case, my comment wasn't meant as a criticism of Peter in any
way, just a relative gauge of the state of the mdb.

Bob

David W. Fenton wrote:
"Bob" <bo*************@gmail.com> wrote in
news:11**********************@y43g2000cwc.googlegr oups.com:
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....


How do you know he couldn't? Do you have the tools to explore the
internal Jet file structure and the knowledge of it to be able to
say that data was not recoverable? Peter does have those tools and
that knowledge, having developed them himself over the years. I
would never presume to guess whether or not a corrupted MDB is
recoverable or not, given that I have no evidence or knowledge that
gives me a chance of making any kind of judgment of just how corrupt
it is internally.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/


Jun 7 '06 #28

This discussion thread is closed

Replies have been disabled for this discussion.