MS Access – Delete Attachments Using a Query

Working on a client’s database, I was in need of deleting the attachments associated to a record and couldn’t make it work using standard DELETE syntax

DELETE MyAttachments
FROM Att

which would generate the following error

An UPDATE or DELETE query cannot contain a multi-valued field

I searched high and low and couldn’t find any direct way to delete attachments using a simple query. Every post I came across always pointed to using VBA code. So I put out an SOS to my fellow Access MVPs and luckily for me Brent Spaulding came to my rescue!

The solution is remarkably obscure, but simple! Use .Value. If we modify the original query to

DELETE MyAttachments.Value
FROM Att

Yes, that simple addition solves the issue! Now, the query will effectively work and delete the attachments.

As per any other query, you can add other standard clauses (WHERE, HAVING).

SQL View Only!

One Important note about this solution however is that you need to create the SQL statement in SQL View.  Access will mess up the syntax if you use Design View.  So you need to create such a query using the text editor, otherwise it will not work.  Just switching from SQL View to Design View will mess up the syntax and generate an error relating to multi-value field as shown below.

Cannot reference a multi-valued field in an UPDATE or DELETE statement that contains other fields
Attachments/Lookup Fields/...
This once again goes to illustrate why Attachment fields, Lookups, and any other multi-value field should be avoided like the plague. Microsoft has implemented things in such a poor manner that you can’t natively manage them! Don’t get me wrong, luckily for me Brent had a workaround for me, but in reality for the average user, there is no solution since they can’t build such a query using the QBE.

Simply put multi-value fields (MVF) broke Access. When you can no longer manage database field values using standard queries, you have broken the database, and that is exactly what Microsoft did several years ago. In an effort to simplify and dumbdown Access, they effectively made it unmanageable and actually created a bigger problem than they ever fixed.

I’ll just end by saying a great big emphatic THANK YOU to Brent!

2 responses on “MS Access – Delete Attachments Using a Query”

  1. Mark

    Awesome! This is exactly what I was looking for! Thanks so much!

    And I agree with your comments about Microsoft and Access. I much prefer working with a true RDBMS like DB2 or Oracle (or even SQL Server).

  2. Joe Ferrer

    I just ran into this myself. In my case I am deleting all rows of the table (DELETE FROM tblname) and noticed that the filesize did not shrink after ‘compact and repair’. The attachments did not delete from the table! In order to delete all, you will need to run two deletes, first delete all attachments then delete all rows. Thanks Microsoft!