title: MS Access: Running a delete query for a many-to-many relationship containing attachment field; from a button
body: <p>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:</p>
  <p><a href="https://i.stack.imgur.com/qZjbI.png" rel="nofollow noreferrer">DB Image</a></p>
  <p>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.</p>
  <p>When I try to run a delete query however, I get the error message that</p>
  <blockquote>
  <p>Access cannot delete a record containing a multivalued field</p>
  </blockquote>
  <p>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?</p>
  <p>SQL code for the full query:
  cannot delete multivalue field</p>
  <pre><code>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));
  </code></pre>
  <p>SQL code for only the table containing the attachments:
  cannot delete multivalue field</p>
  <pre><code>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));
  </code></pre>
  <p>SQL code for deleting only the record in the link table:
  works fine, no errors, no message</p>
  <pre><code>DELETE sdb_link.[stoff_id], sdb_link.[sdb_id]
  FROM sdb_link
  WHERE (((sdb_link.[sdb_id])=1));
  </code></pre>
  <p>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.</p>
  <p>Appreciate any help, pretty new to access, so maybe it's a simple thing.</p>