First Published 12 January 2025                                                Difficulty level :   Moderate


Section Links:
          Introduction
          The Attachment Field Type
          Problems with Attachment Fields
          Alternative to Attachment Fields
          Feedback



Introduction                                                                                                                   Return To Top

This is the third in a series of articles about the complex datatypes that were first introduced in Access 2007. This article discusses the Attachment field type.

The two previous articles were:
•   multivalued fields (MVFs)     - see Multivalued Fields . . . and why you shouldn't use them
•   column history memo fields  - see Use Column History in Memo fields

Each of these has features that may seem initially appealing (especially to new Access developers).
However, they all have major disadvantages and most experienced Access developers avoid them completely.


The Attachment Field Type                                                                                           Return To Top

Attachment fields are designed to add one or more files, documents, presentations, images etc, to the records in your Access database.
The official Microsoft article on attachment fields is available at: Attach files and graphics to the records in your database

The Attachment field type can be selected when designing a table:


In datasheet view, a paper clip symbol shows the number of files attached to the records. In other views, you may see a symbol representing one of the attached file types.

Files can then be attached to selected records in a table, query or form by double clicking on the attachment field


Click the Add button, browse to the file required and click Open to attach the file. Repeat the process for other files as required

When you have added one or more files, the other buttons are enabled, allowing you to open, remove or save files.


NOTE:
1.   The feature supports a wide range of file types. However certain file types are considered 'unsafe' and cannot be attached.
      These include Access databases (.accdb / .mdb), registry files (.reg) and executable files (.exe)


      You can often compress 'blocked' file types and then attach as a zip file but this isn't recommended.

2.   As you can attach multiple files to each record, the attachment feature is a type of multivalued field and has all the issues associated with MVFs. See the article linked above.

3.   All these attached files are now stored in your Access database, increasing the size of your database.
      However, when you click Open to view the file, it will open in the default application associated with that file type e.g. Word / Excel / Notepad.

4.   An attachment field (Data) is used in an Access system table, MSysResources:



This is used to store Access theme info and details of all shared images stored in the image gallery.


However, in this usage, each record only ever stores one piece of imformation.



Problems with Attachment Fields                                                                                     Return To Top

Unfortunately, there are many important issues with attachment fields. These include:

a)   Increased Database Size:
      Storing files can quickly bloat your database, leading to slower performance and potential storage issues.
      Each file that is added will increase the database file size by AT LEAST that amount. For certain file types e.g. .bmp, the size increase is far larger than the size of the attached file itself.

      If several large files are attached, the database size may easily exceed 1 GB and performance will be noticeably adversely affected.
      Eventually, you will hit the Access database size limit of 2 GB (though the database may become unusable before that point!)

b)   Normalization Issues:
      Using attachment fields can violate the principles of database normalization, especially if you're storing multiple attachments in a single record.
      This can lead to data redundancy and make your database harder to maintain.

c)   Hidden Attached Tables:
      For each attachment field, Access creates a deep hidden attached table to store the actual data of attachments as normalized data.
      This obscures effective data management, making it more challenging to directly access and manage the normalized data.

      The attached tables have names starting 'f_' followed by a long string then another underscore and the AttachmentFieldName e.g. f_D4788154E1BE40D191EA5D3A0529C43E_Attach
      For example, the screenshot below shows two tables with attachment fields together with the hidden attached tables associated with each


      The attached tables cannot be viewed directly by any standard method. This built-in protection is designed to ensure the tables cannot easily be tampered with.

      However, I have used a bit of trickery below to display the contents of a f_..._Data table associated with the attachment Data field in the MSysResources system table.


      Notice this has a total of 8 fields although three of these are always empty

d)   File Duplication:
      If you need to use the file outside of Access, you will need to keep a separate copy externally resulting in unnecessary duplication and wasted storage space.

      If you are certain this isn't needed, you could delete the external file after attaching it in Access.
      However if you later delete the record containing one or more attached files, you will also delete the file(s) themselves.

e)   Complexity in Queries and Reports:
      Handling attachment fields makes writing queries and generating reports more complicated, as you have to manage the binary data.

      When a query is created on a table with an attachment field, a '+' symbol appears next to the attachment field. Click this to expand the view, showing 3 of the attached table fields


      The attached table fields can be included in the query:


      The screenshot shows that a separate record is created in the attached table for each attached file

      NOTE: In older versions of Access, when the query wizard was used to design the query, it showed 3 additional fields from the attached table:


      All these additional fields could be selected in the query wizard even though they are missing in the query design window:


      However, selecting these additional fields was pointless as the extra fields are always empty.


      I reported this issue to the Access team in 2023 as being confusing to end users. The wizard has since been amended in current versions of Access so that this issue no longer occurs.


      Action queries involving tables with attachment fields can be problematic even where any filter criteria are on standard field types
      For example, lets say I want to delete the second record in this table where Field1 ='def'


      One way of doing this would be to filter the delete query where Left(Field1, 1) ='d'. However using an expression in a query with an attachment / MVF field causes an error:



f)   Limited Compatibility:
      Most database management systems or applications do NOT support attachment fields, which can cause problems when sharing your database with others.
      Upsizing data with attachment fields to SQL Server is possible but can be difficult to do successfully. There is usually a better solution (see below)

g)   Backup and Restore Challenges:
      Backing up and restoring databases with large attachment fields can be cumbersome and time-consuming.
      Large databases are also more likely to experience corruption.



Alternative to Attachment Fields                                                                                 Return To Top

      There is a very simple alternative that solves all the above problems.

      Store the path to the files in your table(s) rather than attach the files and use code such as Application.FollowHyperlink followed by the file path to open the file in its default application.
      The Access database file size will be much smaller and performance will be improved.



Feedback                                                                                                                         Return To Top

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 12 January 2025

Return to Access Articles Page

Return to Top