Why is this not updateable???

[Kenln]

I cannot figure this out.

UPDATE tbl_Vendor_Release_Info 
INNER JOIN CMSqry_Vendor_and_Contacts 
ON (tbl_Vendor_Release_Info.Vendor_No = CMSqry_Vendor_and_Contacts.VENDOR_NO) 
AND 
(tbl_Vendor_Release_Info.Div_No = CMSqry_Vendor_and_Contacts.DIV_NO)
AND
(tbl_Vendor_Release_Info.Co_No = CMSqry_Vendor_and_Contacts.CO_NO) 

SET tbl_Vendor_Release_Info.Vendor_Name = [CMSqry_Vendor_and_Contacts].[vendor_name]

tbl_Vendor_Release_Info is a table on MSSQL with Co_No; Div_No and Vendor_No as PKs.

CMSqry_Vendor_and_Contacts is a pass-through query with no (?) pk.

I would prefer to normalize the db and not include the Vendor_Name (Contact, Tele, etc...) in the MSSQL table at all, but for other reasons I just can't seem to get it. That however is another problem (probably due to my inexperience).

 

[The_Doc_Man]

In general, a query is not updateable if there is a reason for the targeted record to be ambiguous or if one of the components is an underlying aggregate.

Ambiguous: If the combined selection criteria don't trim the selection down to a single record. I.e. instead of doing an UPDATE do a select. How many records come back?

If the answer is 0, you have a hint right there as to what is wrong.

If the answer is "multiple" - I recall a post by Pat Hartman addressing the issue that if one of the tables isn't indexed, Access cannot KNOW ahead of time whether it is a unique update or not. If you wanted ALL matching records to be updated, I guess that should still work, but if Access is confused, it won't work. When dealing with pass-through queries, I am really unsure of all the complexities added by that wrinkle because I haven't done that sort of thing before.

 

[Kenln]

If I add a where statement I can get it down to one record. It still gives me the same message.

 

[Kenln]

The only solution I have found (and I do not like it) it to create a temporary table that is appended to each time the db is open and have that update the vendor table in MSSQL and still worry about a multi-user enviroment.

 

[Banana]

If I add a where statement I can get it down to one record. It still gives me the same message.

Just because you can get down to one row, does mean it is updateable. The real criteria, as The_Doc_Man has told you, is whether Access can trace it to only one row from *every* source.

If you google "Allen Browne Updateable query", he has written a great explanation on what to look out for factors that could render a query non-updateable.

 

[boblarson]

Just because you can get down to one row, does mean it is updateable. The real criteria, as The_Doc_Man has told you, is whether Access can trace it to only one row from *every* source.

If you google "Allen Browne Updateable query", he has written a great explanation on what to look out for factors that could render a query non-updateable.

And to save you from having to Google:

http://allenbrowne.com/ser-61.html (I have it bookmarked )

 

[Kenln]

If I had to guess it would be
"The fields in a JOIN are not indexed correctly: there is no primary key or unique index on the JOINed fields."

The Pass-Through query does not show any PKs at all. Is there a way to set any of the fields to (as) a PK?

 

[LPurvis]

>> "CMSqry_Vendor_and_Contacts is a pass-through query with no (?) pk"

Passthroughs are, by definition, read only.
For a Jet query to be updatable it must select from only updatable sources.
(The fact that it returns no PK, alone, would render Access unable to update it even if it were possible).
You could, perhaps, create a view on the server and link to that. But again, you'll need to specify a unique index in Access when doing so.