<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Message</TITLE>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2600.0" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>So what's the error Roz -- are you getting an error
or just the stand by message in the status bar? </FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>Susan H. </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT><BR> </DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV><SPAN class=917073213-02042003><FONT face=Arial color=#0000ff
size=2>Susan</FONT></SPAN></DIV>
<DIV><SPAN class=917073213-02042003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=917073213-02042003><FONT face=Arial color=#0000ff size=2>All
spot-on, unfortunately the query does meet all of the conditions you mention.
Cascade updates are not available for linked SQL tables, however.
</FONT></SPAN></DIV>
<DIV><SPAN class=917073213-02042003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=917073213-02042003><FONT face=Arial color=#0000ff size=2>I'll
bet this will be helpful for someone else at some point!</FONT></SPAN></DIV>
<DIV><SPAN class=917073213-02042003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=917073213-02042003><FONT face=Arial color=#0000ff
size=2>Thanks</FONT></SPAN></DIV>
<DIV><SPAN class=917073213-02042003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=917073213-02042003><FONT face=Arial color=#0000ff
size=2>Roz</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B> Susan Harkins
[mailto:harkins@iglou.com] <BR><B>Sent:</B> 01 April 2003
17:16<BR><B>To:</B> <A
href="mailto:accessd@databaseadvisors.com">accessd@databaseadvisors.com</A><BR><B>Subject:</B>
Re: [AccessD] Updating queries with linked tables<BR><BR></FONT></DIV>
<DIV><FONT face=Arial size=2>Roz, not all queries are updateable. Here are
some general rules:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>A query based on a single table is updateable
if:</FONT></DIV>
<DIV><FONT face=Arial size=2> The query contains no aggregate functions
or refers to a query that contains an aggregate function. </FONT></DIV>
<DIV><FONT face=Arial size=2> The query's Unique Values property is set
to No.</FONT></DIV>
<DIV><FONT face=Arial size=2> The query doesn't include a GROUP BY
clause.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>A multi-table query with a one to one
relationship is updateable provided all of the above conditions are true.
</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>A multi-table query with a one to many
relationship is updateable on both sides as long as you don't violate
referential integrity rules, and provided the above conditions are true. Try
a</FONT><FONT face=Arial size=2>dding the foreign key value to the query --
did I read where you'd tried that? If that's the case, check referential
integrity -- if it's enabled, also enable the cascade updates between the
two tables. </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Keep in mind that the following aren't
updateable, regardless: Crosstab, pass-through, and UNION (that's why I
asked for the clarification about the word combine), multi-table queries
based on three or more tabls in a many to one to many relationship, Totals
view. </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Hope something there helps. </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Susan H. </FONT></DIV>
<DIV><FONT face=Arial size=2> </FONT><FONT face=Arial
size=2> </FONT></DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=roz.clarke@donnslaw.co.uk
href="mailto:roz.clarke@donnslaw.co.uk">Roz Clarke</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A
title=accessd@databaseadvisors.com
href="mailto:'accessd@databaseadvisors.com'">'accessd@databaseadvisors.com'</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Tuesday, April 01, 2003 10:52
AM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> RE: [AccessD] Updating
queries with linked tables</DIV>
<DIV><BR></DIV>
<DIV><SPAN class=731115215-01042003><FONT face=Arial color=#0000ff
size=2>No blank record, all fields locked. </FONT></SPAN></DIV>
<DIV><SPAN class=731115215-01042003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=731115215-01042003><FONT face=Arial color=#0000ff
size=2>It wouldn't surprise me if this is just a
'feature'.</FONT></SPAN></DIV>
<DIV><SPAN class=731115215-01042003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=731115215-01042003><FONT face=Arial color=#0000ff
size=2>R.</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B> Susan
Harkins [mailto:harkins@iglou.com] <BR><B>Sent:</B> 01 April 2003
16:25<BR><B>To:</B> <A
href="mailto:accessd@databaseadvisors.com">accessd@databaseadvisors.com</A><BR><B>Subject:</B>
Re: [AccessD] Updating queries with linked tables<BR><BR></FONT></DIV>
<DIV><FONT face=Arial size=2>Well, when you say "combine" -- I think of
append or UNION. Relationships "join" records -- nothing wrong with the
way you siad it -- I just thought I'd check to make sure. So here's the
next question -- how do you know it's not updateable? Are you getting an
error message? Is there a blank (new) record at the bottom of the query
in Datasheet View?</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Susan H. </FONT></DIV>
<DIV><BR> </DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV><SPAN class=133000109-01042003><FONT face=Arial color=#0000ff
size=2>Standard left outer join on staff ID - is there another way?!
</FONT></SPAN></DIV>
<DIV></DIV>
<DIV><FONT face=Tahoma><FONT size=2><SPAN
class=133000109-01042003><FONT face=Arial color=#0000ff>*bangs head on
desk*</FONT></SPAN></FONT></FONT></DIV>
<DIV><FONT face=Tahoma><FONT face=Arial color=#0000ff size=2><SPAN
class=133000109-01042003></SPAN></FONT></FONT> </DIV>
<DIV><FONT face=Tahoma><FONT face=Arial color=#0000ff size=2><SPAN
class=133000109-01042003>R.</SPAN></FONT></FONT></DIV>
<DIV><FONT face=Tahoma><FONT size=2><SPAN
class=133000109-01042003></SPAN></FONT></FONT> </DIV>
<DIV><FONT face=Tahoma><FONT size=2><SPAN
class=133000109-01042003> </SPAN>-----Original
Message-----<BR><B>From:</B> Susan Harkins [mailto:harkins@iglou.com]
<BR><B>Sent:</B> 31-Mar-2003 17:20<BR><B>To:</B> <A
href="mailto:accessd@databaseadvisors.com">accessd@databaseadvisors.com</A><BR><B>Subject:</B>
Re: [AccessD] Updating queries with linked
tables<BR><BR></DIV></FONT></FONT>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV><FONT face=Arial size=2>How are you combining your tables?
</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Susan H. </FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>What I'm finding (and I can't believe I
never hit this before) is that although the SQL tables can be
updated through the Access FE, and the local tables can be updated,
combine them and wallop, not updateable. Even if all the key fields
are included in the query.</FONT></DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px"><FONT
face=Arial size=2></FONT> </BLOCKQUOTE></BLOCKQUOTE>
<P>
<HR>
<P></P>_______________________________________________<BR>AccessD
mailing
list<BR>AccessD@databaseadvisors.com<BR>http://databaseadvisors.com/mailman/listinfo/accessd<BR>Website:
http://www.databaseadvisors.com<BR></BLOCKQUOTE></BLOCKQUOTE>
<P>
<HR>
<P></P>_______________________________________________<BR>AccessD mailing
list<BR>AccessD@databaseadvisors.com<BR>http://databaseadvisors.com/mailman/listinfo/accessd<BR>Website:
http://www.databaseadvisors.com<BR></BLOCKQUOTE></BLOCKQUOTE>
<P>
<HR>
<P></P>_______________________________________________<BR>AccessD mailing
list<BR>AccessD@databaseadvisors.com<BR>http://databaseadvisors.com/mailman/listinfo/accessd<BR>Website:
http://www.databaseadvisors.com<BR></BLOCKQUOTE></BODY></HTML>