我有一个小的访问数据库，其中包含化学药品和相应的安全数据表作为治疗类型。它们与化学物质的多一关系关系。 DB看起来如下：
db image
我想创建一个让用户删除的删除查询来自[SDB_TABELLE]的所选记录包含附件，以及链接表[SDB_Link]中的相关行（由[SDB_ID]识别）。理想情况下，这应该从按钮的on_click事件发生。
但是，当我尝试运行删除查询时，我会收到错误消息
访问无法删除包含多值字段的记录
，即附件字段[datei] afaik。有什么办法解决吗？我真的很想保留此项目的附件类型字段。我可以从链接表中删除该行，只有附件字段的一个不起作用。从我阅读的多价字段中，从技术上讲，它就像是一种多对多的关系，因此应该有某种方法可以以正确的顺序删除嵌套元素？
完整查询的SQL代码：
无法仅删除
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代码：
多价字段
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代码：
工作正常，没有错误，没有消息
DELETE sdb_link.[stoff_id], sdb_link.[sdb_id]
FROM sdb_link
WHERE (((sdb_link.[sdb_id])=1));
对向导进行查询，以防万一很重要。我计划将标准更改为包含当前记录的SDB_ID字段中一个字段中的值，但是到目前为止，我已经粘在此附件上，因此我还无法对其进行测试。
感谢任何帮助，非常新的访问，所以也许这很简单。
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）
我找到了这个答案：链接
在这里另一个Microsoft多值字段指南
（这不是查询，而是可以帮助您了解多估字。）：
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.