I have a small access database containing chemicals and corresponding safety datasheets as attchment type. They are related in a many-to-many relationship to the chemicals. The DB looks as follows:
I wanted to create a delete query that lets the user delete the selected record from [sdb_tabelle] containing the attachment, as well as the related row in the link table [sdb_link] (identified by [sdb_id]). Ideally this should happen from the on_click event of a button.
When I try to run a delete query however, I get the error message that
Access cannot delete a record containing a multivalued field
which is the attachment field [datei] afaik. Is there some way around this? I would really like to keep the attachment type field for this project. I can delete the row from the link table just fine, just the one with the attachment field does not work. From what I have read a multivalued field is technically like a many-to-many relationship, so there should be some way to delete the nested elements in the correct sequence, or not?
SQL code for the full query: cannot delete multivalue field
DELETE sdb_link.stoff_id, sdb_link.sdb_id AS sdb_link_sdb_id, sdb_tabelle.sdb_id AS sdb_tabelle_sdb_id, sdb_tabelle.version, sdb_tabelle.datum_aktualisiert, sdb_tabelle.datum_upload, sdb_tabelle.datei, sdb_tabelle.datei.FileData, [sdb_tabelle].[datei].[FileFlags] AS Ausdr1, sdb_tabelle.datei.FileName, [sdb_tabelle].[datei].[FileTimeStamp] AS Ausdr2, sdb_tabelle.datei.FileType, [sdb_tabelle].[datei].[FileURL] AS Ausdr3, sdb_tabelle.sprache_id, sdb_tabelle.kommentar
FROM sdb_tabelle INNER JOIN sdb_link ON sdb_tabelle.[sdb_id] = sdb_link.[sdb_id]
WHERE (((sdb_link.sdb_id)=1) AND ((sdb_tabelle.sdb_id)=1));
SQL code for only the table containing the attachments: cannot delete multivalue field
DELETE sdb_tabelle.[sdb_id], sdb_tabelle.[version], sdb_tabelle.[datum_aktualisiert], sdb_tabelle.[datum_upload], sdb_tabelle.[datei], sdb_tabelle.[datei].[FileData], [datei].[FileFlags] AS Ausdr1, sdb_tabelle.[datei].[FileName], [datei].[FileTimeStamp] AS Ausdr2, sdb_tabelle.[datei].[FileType], [datei].[FileURL] AS Ausdr3, sdb_tabelle.[sprache_id], sdb_tabelle.[kommentar]
FROM sdb_tabelle
WHERE (((sdb_tabelle.[sdb_id])=1));
SQL code for deleting only the record in the link table: works fine, no errors, no message
DELETE sdb_link.[stoff_id], sdb_link.[sdb_id]
FROM sdb_link
WHERE (((sdb_link.[sdb_id])=1));
The queries were done with the wizard, in case that matters. I plan to change the criterion to the value in a field on my form that contains the sdb_id of the current record, but so far I am stuck on this attachment thing so I wasn't able to test it out yet.
Appreciate any help, pretty new to access, so maybe it's a simple thing.
CodePudding user response：
In Tools/Relationsships menu you can estabilish that a record deleted in table will cause an automatic deletion of related records in other table. See how "delete cascade option" works.
CodePudding user response：
I found this answer: Link
Here another Microsoft guide for multivalued fields (This one is not by query but can help you to understand multivalued fields.): Link