<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<TITLE>Message</TITLE>
<META content="MSHTML 6.00.2800.1106" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><SPAN class=584445115-02042003><FONT face=Arial color=#0000ff size=2>no
error, no nothing, just locked fields, not even a 'this recordset is not
updateable' message in the status bar.</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> 02 April 2003 16:33<BR><B>To:</B>
accessd@databaseadvisors.com<BR><B>Subject:</B> Re: [AccessD] Updating queries
with linked tables<BR><BR></FONT></DIV>
<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></BLOCKQUOTE></BODY></HTML>