MS Access: Running a delete query for a many-to-many relationship containing attachment field; from a button

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:

DB Image

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.

2 Answers

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

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.