Why is this Query not updateable? (1 Viewer)

[darbid]

Why is this Query not updateable? (Many to Many Relationship) (Edited)

Hi guys,

I am working with a MSAccess 2003 (mdb) and have a SQL Server 2000 backend.

The following query works for me, (which for me is an achievement and I deserve a knock-off beer just for that) but I would like to use it as the record source for a subform.

It is not updatable.

To remove any form issues I put this as a query and tried to edit the result but even this is not updateable.

SELECT qc_comment, qc_lc, qc_marked
FROM tbl_questionnaire AS q 
INNER JOIN tbl_quest_comment AS c 
ON (q.q_decision_number=c.qc_decision_number) 
AND (q.q_bkz_id=c.qc_bkz_id) 
AND ((q.q_em_akten_id=c.qc_em_akten_id And q.q_type='em') 
Or (q.q_srf_akten_id=c.qc_srf_akten_id And q.q_type='srf'))
WHERE qc_lc='BOW';

Am I doing something wrong?

EDIT: The issue is that these joins are Many to Many - I reduced it down to only one join but it still will not update. I also tried it with tables in Access alone and it is not updateable.

As I understand it Access cannot update the resulting query if there is a Many to Many relationship. So what is the workaround or what do I have to do?

 

[jdraw]

I'm sure you have seen this, but for what it's worth here is Allen Browne's material.
http://allenbrowne.com/subquery-02.html

 

[darbid]

I think I fired off this question a bit quick and without thinking.

I have wasted so much time on this and I am not even sure I need such a SQL anymore.

In any case in my research I have found that an SQL with a many to many relationship is not updateable.

One workaround is to set the properties to "Dynaset (Inconsistent Updates)".

For me this seemed to work and I could not work out what INCONSISTENT things I would get. Nor could I find a straight answer on the risks of this setting.

In the end I have changed how I am doing the SQL totally to avoid this problem.

 

[Galaxiom]

Never used one but I think Inconsistent Updates allows the key to be changed on a record essentailly allowing the child records to be connected to a different master.

 

[Galaxiom]

Normally non-updateable situations would be dealt with using subforms.