Debugging Locked Dynaset

[theConjurian]

I am working on an application for a talent agency. The top three tables are customers, event, entertainers (think customers, orders, order lines). The primary keys are as follows:

Customers: ContactID
Events: ContactID, ReferenceNo
Entertainers: ReferenceNo, LineNo

The Customer detail form has a subform on it whose query contains a join:

SELECT Events.*, Events.EventDate, Entertainment.*, Entertainment.StartTime
FROM Events INNER JOIN Entertainment ON Events.ReferenceNo = Entertainment.ReferenceNo
ORDER BY Events.EventDate DESC , Entertainment.StartTime DESC;

The query is not updateable and I cannot figure out why. I am looking for some suggestions as to how to fix this. I'm not sure what it was that I changed that made it stop working.

 

[RickSpr]

Is this query updateable outside the context of the subform? That is, if you open the query itself (build one from the SQL statement if you're using SQL in the subform's RecordSource) is it updateable?

If the query is updateable when opened separately, check the subform control's LinkMasterFields and LinkChildFields properties. If they're not set correctly, the subform probably won't show any records and you won't be able to add any.

Note: Since you have Events.* in the column list, you don't need Events.EventDate also. Similarly, you don't need Entertainment.StartTime. Including these separately gives you duplicate columns, and these duplicates will be given names like Expr001 and Expr002 because each column has to have a unique name. (I'm guessing you included them because you're coming from another DBMS where columns referenced in the WHERE clause have to be explicitly named in the column list.) I wouldn't think this would be causing the query to be non-updateable--Access is pretty smart about preserving updatability--but you shouldn't need them anyway, so I'd take them out. Rick Sprague

 

[theConjurian]

I should have mentioned this in the earlier post ... the query (outside the context of the form/subform) is not updateable either.

As you suggested, I had trimmed the query down after I made my post. It now reads

SELECT Events.*, Entertainment.* FROM Events INNER JOIN Entertainment
ON [Events].[ReferenceNo]=[Entertainment].[ReferenceNo]
ORDER BY [Events].[EventDate] DESC , [Entertainment].[StartTime] DESC;

In the context of the subform, it does return the appropriate records.

Until I figure this out, I have set the Recordset Type to Dynaset (Inconsistent Updates) with Record Locks set to Edited Records.

Structurally, double-clicking on the subform opens a "detail" form with the event information (one table).
It works, and if I refresh the query when I close this form, the subform is correctly refreshed.

I don't, however, have an appreciation of the implications of using "Inconsistent Updates".

 

[RickSpr]

I think I see your problem--it's in the keys. Neither the Events table nor the Entertainers table contains the complete key of the other. This makes the relationship between them many-to-many. Such queries can't be updateable.

To see why, consider that Events might have two rows as follows:

    ContactID   ReferenceNo
       116          27
       212          27

Similarly, Entertainers might have the following two rows:

    ReferenceNo LineNo
       27         1
       27         2

The join between these tables will then have 4 rows for this reference number:

    ContactID   ReferenceNo  LineNo
       116          27         1
       116          27         2
       212          27         1
       212          27         2

Now say you updated the first row, changing the ReferenceNo to 33. If this ReferenceNo column was the one from the Events table, Access would need to update Events.ReferenceNo. But now the Events.ReferenceNo no longer matches the Entertainers.ReferenceNo. Since only matching data should appear in this result table, the whole row must now disappear--and so must row 2, because it uses the same row from Events! Well, relational tables just don't behave this way--rows don't disappear from them just because you updated a value. So updates are not allowed where a many-to-many relationship exists.

You might think that Access should automatically update Entertainers.ReferenceNo to 33 as well. Unfortunately, that would be no better. If it did that, row 3 of the result table would have to disappear, because it uses the same Entertainers row as row 1, which you just updated so it no longer matches Events.ReferenceNo in row 3. Again, the update in row 1 would force other rows to disappear.

Ultimately, the problem is either an incorrect selection of primary key columns, or in trying to do an ambiguous update in the subform. Not knowing what your tables represent in the real world, I can't be sure, but I think it's the latter. From a relational point of view, the Events table is a many-to-many association between customers and entertainers. But the fact that you call it "Events" suggests that you also intend it to represent events. It doesn't do that: an event is not defined by a combination of one customer with one entertainer. I suspect you're missing a table, and its corresponding key. You need to think out the real-world relationships (one-to-many's) in what you're trying to represent. Once you get the tables right, you should be able to see how the form/subform structure needs to change to accomodate it.


BTW, the effect of Inconsistent Updates is that you will be allowed to violate your relational integrity rules, which you set up in the Relationships window when you create the relationships. What this means is that, if you delete a row in a parent table, it won't cascade to the child tables, and if you update a foreign key, it will neither cascade nor be checked to ensure there is a related row in the parent table. In the example I gave above, using Inconsistent Updates prevents the problem of disappearing rows by allowing the rows with non-matching reference number (after you update it) to remain in the result table. Unfortunately, that means that as you update the result table, you're breaking matches left and right, and you will wind up with rows in one table that no longer have any match in the other. In reality, it's not too bad as long as you don't update the ReferenceNo column (the join column). The only problem is that when you update data in one row, values in other rows may "mysteriously" change as well. Use of an Inconsistent Updates query is not recommended. I've never found a valid use for one, in years of Jet database design. Rick Sprague