Recordset not updateable !

[dmeid]

I have created a query where it does a left join of a table and a select query. The resulting query returns the correct records, but I am unable to have the option of adding any new records. Many other queries I have created allows the recordset to be updated!

Can anyone give a logical reason why this is happening?

I appreciate your thoughts on this.

 

[Tiro Cupidus]

Because of the join, I suppose. Posting the SQL from your queries would help.

 

[modest]

I agree. Posting your SQL would be more official so we can actually see what you're doing. Anything could be wrong, but you're asking us about theory and system architecture, when it'd be easier to just find a syntax error or play with it ourselves.

POST the SQL!

-modest

 

[dmeid]

Here is my SQL.... as you can see I am doing to 2 Left Joins. Possibly that makes the recordset not updateable?

SELECT tblDetail.*, tblMaster.OnLineDte, tblChecklist.[Permit#]
FROM (tblDetail LEFT JOIN tblMaster ON tblDetail.WellID = tblMaster.WellID) LEFT JOIN tblChecklist ON tblMaster.WellID = tblChecklist.[Property#]
ORDER BY tblPipeLineDetail.PipeLineID, tblPipeLineDetail.MapIndex;

 

[modest]

Dmeid, have you removed the Order By as Pat suggested?

Pat, I don't think this would give a syntax error as you can order by fields you don't list... I think there still might be a problem updating a table on these joins though even if you remove the sort. I'm just curious to see what happens =)

-modest

 

[Jings00]

From my experience, a select query with 2 outer joins will not be updatable unless properties are set to Dynaset (Inconsistent Updates).

 

[dmeid]

Actually I Made an error on the order by on my copy and paste. This is the actual one.

modest..... I did remove the Order by .....but it still did not do anything.

All I can think of is that I have too many tables that I am joining. When I only have 2 left joined tables, I can still have the abiltiy to update/add records, but once I add that 3rd table as left Join, it retrieves the records but is no longer updateable.

-----------------------------------------------------------------

SELECT tblDetail.*, tblMaster.OnLineDte, tblChecklist.[Permit#]
FROM (tblDetail LEFT JOIN tblMaster ON tblDetail.WellID = tblMaster.WellID) LEFT JOIN tblChecklist ON tblMaster.WellID = tblChecklist.[Property#]
ORDER BY tblDetail.PipeLineID, tblDetail.MapIndex;
__________________

 

[modest]

Yeah I think there is a data integrity issue when you update a multi-table join with more than 2 tables. Could you paste your insert/update statement though?

 

[dmeid]

I figured it out, you are right. The reason why I can't edit is because of the referential integrity at the database level, because of my key fields, I have a "1" to many relationship.

Thanks for your help all.

 

[modest]

at the time, i had information to back this up, but I'll go with Pat.

Pat knows all =)