[AccessD] Updating queries with linked tables

Roz Clarke roz.clarke at donnslaw.co.uk
Wed Apr 2 09:53:59 CST 2003


no error, no nothing, just locked fields, not even a 'this recordset is not
updateable' message in the status bar.

-----Original Message-----
From: Susan Harkins [mailto:harkins at iglou.com] 
Sent: 02 April 2003 16:33
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Updating queries with linked tables


So what's the error Roz -- are you getting an error or just the stand by
message in the status bar? 
 
Susan H. 

 

Susan
 
All spot-on, unfortunately the query does meet all of the conditions you
mention. Cascade updates are not available for linked SQL tables, however. 
 
I'll bet this will be helpful for someone else at some point!
 
Thanks
 
Roz

-----Original Message-----
From: Susan Harkins [mailto:harkins at iglou.com] 
Sent: 01 April 2003 17:16
To: accessd at databaseadvisors.com <mailto:accessd at databaseadvisors.com> 
Subject: Re: [AccessD] Updating queries with linked tables


Roz, not all queries are updateable. Here are some general rules:
 
A query based on a single table is updateable if:
 The query contains no aggregate functions or refers to a query that
contains an aggregate function. 
 The query's Unique Values property is set to No.
 The query doesn't include a GROUP BY clause.
 
A multi-table query with a one to one relationship is updateable provided
all of the above conditions are true. 
 
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 adding 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. 
 
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. 
 
Hope something there helps. 
 
Susan H. 
  

----- Original Message ----- 
From: Roz Clarke <mailto:roz.clarke at donnslaw.co.uk>  
To: 'accessd at databaseadvisors.com' <mailto:'accessd at databaseadvisors.com'>  
Sent: Tuesday, April 01, 2003 10:52 AM
Subject: RE: [AccessD] Updating queries with linked tables

No blank record, all fields locked. 
 
It wouldn't surprise me if this is just a 'feature'.
 
R.

-----Original Message-----
From: Susan Harkins [mailto:harkins at iglou.com] 
Sent: 01 April 2003 16:25
To: accessd at databaseadvisors.com <mailto:accessd at databaseadvisors.com> 
Subject: Re: [AccessD] Updating queries with linked tables


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?
 
Susan H. 

 

Standard left outer join on staff ID - is there another way?! 
*bangs head on desk*
 
R.
 
 -----Original Message-----
From: Susan Harkins [mailto:harkins at iglou.com] 
Sent: 31-Mar-2003 17:20
To: accessd at databaseadvisors.com <mailto:accessd at databaseadvisors.com> 
Subject: Re: [AccessD] Updating queries with linked tables



How are you combining your tables? 
 
Susan H. 
 
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.

 



  _____  




_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




  _____  




_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




  _____  




_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030402/b395decf/attachment-0001.html>


More information about the AccessD mailing list