I have joined 2 tables that do not have referential integrity enforced because data in one of the tables does not allow it. They are joined both within table relationships and in the query itself. The query works great for allowing updates to fields in either table. The problem is that when I delete a record using the query, the record gets deleted from the table I do NOT want it deleted from and remains on the table where I WANT it deleted.
The query uses an outer join. I have even tried changing which table is on which side of the join and also tried an inner join, with no change to the delete phenomenon.
Is there any way to specify which table to delete from if the user hits the "Delete Record" icon on the toolbar, or do I have to resort to a command button with some VBA code to get the right record deleted? I can handle the latter, but I wondered if there is a simpler way. (I've been looking for the "simpler way" for 3 hours and haven't found it yet ;-) - and this is on my own time now :0
(Not my preference, but I'm maintaining a database that could use some serious overhauling, but the owners are nonprofit without much money so we're trying quick-and-dirty patch work, which isn't always so quick and invariably gets me dirty.)
The SQL is below (except I deleted some of the fields in the SELECT part, since they don't matter). It doesn't make any difference whether the FROM has "LEFT JOIN", "RIGHT JOIN", or "INNER JOIN" (and all else remains identical) - the record on the table ParSUB ALWAYS gets deleted, and the record on the table ClassTransactionT NEVER gets deleted. Note that this is NOT a delete query. It's a regular Select query, from which a user can hit the "Delete Record" button (or choose Edit-> Delete Record from the menu). I am thinking Access does this because the combination conid+CLID+First is a primary key on the ClassTransactionT table but only an index - and not unique, either - on the ParSUB table (which admittedly could use serious redesign). Is there any quick way to tell Access to delete off ClassTransactionT instead of ParSUB?
SELECT ClassTransactionT.DateT, ClassTransactionT.Paid, ClassTransactionT.conid, ParSUB.Standby
FROM ClassTransactionT LEFT JOIN ParSUB ON (ClassTransactionT.conid = ParSUB.conid) AND (ClassTransactionT.CLID = ParSUB.CLID) AND (ClassTransactionT.First = ParSUB.First)
WHERE (((ClassTransactionT.conid)=[Forms]![Student List]![conid]));
Yes, looks like lack of primary/unique record for second table is it. Make a delete query to fix the proper table IDed after the From keyword, but they still can hit the delete key. Alternatively, create a (continuous) form that has a delete button and do the delete via VB code.
Thanks. I'll figure something out. I'll set the form property to prohibit deletions and make the user click my button to activate a delete query. It's just a little inconsistent with how they delete on other forms, but only one person should even be allowed to delete - but that gets us into the security they don't feel they need badly enough to pay to set up, and that's another topic...