ADP Query Designer Won't Display UPDATE/FROM properly?

[dp]

After looking and looking, it appears that Access ADPs graphic query
designer won't display:

update customer
set [cust_add3] = [customer notes].[note_type]
from customer, [customer notes]
where customer.[cust_code] = [customer notes].[cust_code];

It comes up with the "Query Definitions Differ" dialog box. Anybody know
anything about this? I can live this with I guess, however it was sure
sweet to be able to do this using the graphic designer in MDBs.

Can anyone explain why it doesn't represent them correctly? Is there any
hope to gain this functionality back in the future? I saw a reference in
another post basically saying that

-BrianDP

[Steve Jorgensen]

Perhaps, the designer is expecting a different form of the UPDATE syntax. If
you build it via the designer, then look at the SQL, how does that differ from
the SQL you wrote by hand?

On Tue, 30 Dec 2003 18:01:38 GMT, "dp" <no****@mrspam.com> wrote:

After looking and looking, it appears that Access ADPs graphic query
designer won't display:

update customer
set [cust_add3] = [customer notes].[note_type]
from customer, [customer notes]
where customer.[cust_code] = [customer notes].[cust_code];

It comes up with the "Query Definitions Differ" dialog box. Anybody know
anything about this? I can live this with I guess, however it was sure
sweet to be able to do this using the graphic designer in MDBs.

Can anyone explain why it doesn't represent them correctly? Is there any
hope to gain this functionality back in the future? I saw a reference in
another post basically saying that

-BrianDP

[dp]

I've tried this- But here goes again. You start a stored procedure (I
assume this is how you do action queries since views and functions don't
seem to let you change the query type. I add my two tables, left one is the
table to be updated, and the right table is the table to get the data from.
As soon as I change the query type to Update it asks me which of the two
tables I'm going to update, I tell it the left one, then it removes the
right table, and removes many of the columns in the query designer.

If I create an update statement, using the from clause, with Query Analyzer,
the query executes perfectly, and updates the rows. If I try to paste that
SQL back into my ADP, then it gives me that "Query Definitions Differ"

I'm not really asking IF it can be done with the graphical query designer -
it has become obvious that you can't. My question deals more with the "why"
than the fact that it can't be done. This is the bread and butter of the
type of stuff I did with Access QBE grid against the Jet, and now it seems
I'm going to have to learn SQL syntax. Not that I'm complaining, I should
probably have started with SQL instead of this stupid QBE designer, it's
just that I have people on my development team that are very comfortable
with the QBE, and it's productive for them, even if it produces less than MS
SQL compliant code.

-b
..
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:r4********************************@4ax.com...

Perhaps, the designer is expecting a different form of the UPDATE syntax. If you build it via the designer, then look at the SQL, how does that differ from the SQL you wrote by hand?

On Tue, 30 Dec 2003 18:01:38 GMT, "dp" <no****@mrspam.com> wrote:

After looking and looking, it appears that Access ADPs graphic query
designer won't display:

update customer
set [cust_add3] = [customer notes].[note_type]
from customer, [customer notes]
where customer.[cust_code] = [customer notes].[cust_code];

It comes up with the "Query Definitions Differ" dialog box. Anybody know
anything about this? I can live this with I guess, however it was sure
sweet to be able to do this using the graphic designer in MDBs.

Can anyone explain why it doesn't represent them correctly? Is there any
hope to gain this functionality back in the future? I saw a reference in
another post basically saying that

-BrianDP

[Steve Jorgensen]

On Tue, 30 Dec 2003 20:31:40 GMT, "dp" <no****@mrspam.com> wrote:

I've tried this- But here goes again. You start a stored procedure (I
assume this is how you do action queries since views and functions don't
seem to let you change the query type. I add my two tables, left one is the
table to be updated, and the right table is the table to get the data from.
As soon as I change the query type to Update it asks me which of the two
tables I'm going to update, I tell it the left one, then it removes the
right table, and removes many of the columns in the query designer.

Oops - yes, I remeber what you're talking about now. It was surprising to me
as well. As you already found out, the ADP query designer simply does not
handle update queries involving multiple tables. It also doesn't handle the
frequently useful CASE expressions.

Sorry I don't have anything more useful to offer on this.

[dp]

I wonder if anyone at Microsoft has any comments on this?

-b

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:mt********************************@4ax.com...

On Tue, 30 Dec 2003 20:31:40 GMT, "dp" <no****@mrspam.com> wrote:

I've tried this- But here goes again. You start a stored procedure (I
assume this is how you do action queries since views and functions don't
seem to let you change the query type. I add my two tables, left one is thetable to be updated, and the right table is the table to get the data from.As soon as I change the query type to Update it asks me which of the two
tables I'm going to update, I tell it the left one, then it removes the
right table, and removes many of the columns in the query designer.
Oops - yes, I remeber what you're talking about now. It was surprising to

me as well. As you already found out, the ADP query designer simply does not
handle update queries involving multiple tables. It also doesn't handle the frequently useful CASE expressions.

Sorry I don't have anything more useful to offer on this.