0

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.

PATA
  • 49
  • 8

2 Answers2

0

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.

0xD
  • 46
  • 2
  • 6
  • I'd tried that before but it does unfortunately not solve the issue, which is why i turned it off again and tried to do it manually. Deleting from the link table only does not cascade delete records from the [sdb_tabelle] table, only from the link table, even though the setting for the relationship is on. trying to delete the record manually from only the [sdb_tabelle] table results in the same message as before. – PATA Jun 17 '22 at 09:59
  • The automatic cascade deletion opers only in N-side of a 1toN relationship. Then, deletions in [sdb_tabele] will delete several records in [sdb_dblink] table, but the inverse not occurs. Unfortunality, if you need to disable the automatic mode, then, all related records must be deleted first in [sdb_link]. After it, in posterior action, you can delete that unique-key record in [sdb_tabelle]. TIP: You can make two distict deletion queries and create a Macro that run theese two queries in this right sequence. – 0xD Jun 17 '22 at 13:36
  • OK, I thought it would delete the other way around too, my bad. But that still leaves the issue of the multivalued field in the [sdb_tabelle]. I can make a macro that first deletes the record from [sdb_link] and then TRIES to delete from [sdb_tabelle] (after looking up how to write macros). But it will fail because of the attachment field anyway and tell me it cannot delete a record containing a multivalued field since trying to run a delete query for just the [sdb_tabelle] on its own fails as well for the same reason. – PATA Jun 18 '22 at 13:13
  • I dont understand! You can delete a record containing a multivalued field in a query. I do that. What version of MS-Access do you use? And about Macros, they are not vb-code. You wil easily create a new macro with two lines, with one comand per line each, to execute the two previous defined delete query. – 0xD Jun 18 '22 at 14:05
  • The SQL code for the queries I used to get the error is attached to the question above. When running them I get the error message: Access cannot edit or delete records containing multivalued fields. I changed the query to the solution linked above and now it works. I literally just changed the first line to "DELETE sdb_tabelle.*". I have no idea why it does not work the way I did it first. My version of access: Microsoft® Access® for Microsoft 365 MSO (Version 2205 Build 16.0.15225.20172) 64-bit – PATA Jun 18 '22 at 15:34
0

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

I'm Not A Robot
  • 296
  • 3
  • 12
  • Wow, that actually worked! Thanks a lot! Not sure why though. Shouldn't using DELETE sdb_tabelle.* be the same as calling all the fields manually, as I have done before? What's the difference there? – PATA Jun 18 '22 at 13:30