Why You Should Avoid Using Attachment Data Type in Microsoft Access
The Attachment data type, introduced in Access 2007, allows users to insert files which get embedded directly into databases. However, this feature comes with significant drawbacks:
Database Bloat
Using Attachment fields causes rapid database size increase. Each attached file adds its full size to the database, quickly turning a small database into a large one.
Performance Impact
Large databases resulting from Attachment fields can negatively affect performance, slowing down initial load times, queries and overall operations.
Complex Interaction
Programmatically managing Attachment fields requires specialized code, making it challenging to insert, extract, or manipulate attached files.
Recommended Alternative
Instead of using Attachment fields, the preferred method still remains to keep the files (documents, workbooks, PDFs, images, videos, …) on the PC and simply to store file paths and filenames (including extensions) in the database as plain text. This approach:
- Minimizes the database size
- Maintains better performance
- Simplifies file management and access
- Ensure compatibility with older systems
By avoiding Attachment fields and using file paths instead, you create more efficient, performant database.
Disclaimer/Notes:
If you do not have Microsoft Access, simply download and install the freely available runtime version (this permits running MS Access databases, but not modifying their design):Microsoft Access 2010 Runtime
Microsoft Access 2013 Runtime
Microsoft Access 2016 Runtime
Microsoft 365 Access Runtime
In no event will Devhut.net or CARDA Consultants Inc. be liable to the client/end-user or any third party for any damages, including any lost profits, lost savings or other incidental, consequential or special damages arising out of the operation of or inability to operate the software which CARDA Consultants Inc. has provided, even if CARDA Consultants Inc. has been advised of the possibility of such damages.
Download
Feel free to download a copy (unlocked accdb) by using the link provided below:
Download “How to Handle Attachments Demo in MS Access” HowToHandleAttachments_Demo.zip – Downloaded 18039 times – 67.12 KBThe demo features two closely related approaches and includes all the necessary coding components (such as file selection, opening files, deleting files, etc.). Feel free to explore it and modify it to suit your requirements.
Version History
| Version | Date | Changes |
|---|---|---|
| V1.000 | 2016-10-03 | Initial Public Release |
| V1.001 | 2022-04-03 | Added Conditional Compilation to the API to make it compatible with both 32 and 64-bit versions of Access |
| V1.002 | 2022-09-28 | Split the database to properly reflect multi-user setup Adapted ‘Server’ code to properly place attachments in a sub-folder of Back-End folder |
| V1.003 | 2024-02-17 | Removed any limiting copyright notices. You may use any part of this application for your own purposes. Thank you to George Bowyer for bringing this issue to my attention! |
Hi,
What do you think to the idea of having a separate back end to only store attachments?
Thanks
Tom
I’ve seen people suggest this in the past, as it can limit potential issues to just the attachments, thus leaving the data intact. Personally, I still wouldn’t use such an approach and will stick with storing paths as text entries.
It’s one thing if you only need to store a couple attachments, things like logos, etc, but if you need to attach various files as part of entries within your database on a regular basis, I wouldn’t ever consider using the Attachment data type. I have seen too many people post questions regarding hitting the 2GB file size limit because of attachments, rendering their database unusable! Simply not worth the risk when the alternative is so easy to implement.
I strongly agree with you: never use this kind of data field!
Hi,
thanks for such a great article. I would like to use this method in a back end data tables, front end interface setup.
I’ve put the tables into the 3 tables from your example into my back end database, linked them into the front end where I have also placed the forms and the modules.
I’m having an issue with the form throwing a compile error, “Variable not defined” , when I click the elipses on the form to kickstart the file selection dialogs.
In the debug screen it is highlighting ‘msoFileDialogFilePicker’ as being the cause.
Have you any advice?
Thanks,
Barry
Have you set an Reference to Microsoft Office XX.X Object Library? Or I have a Late bound version also available on my site.
Excellent Article!!! It was very helpful..
Just one thing I wanted to know as to how do I send the attachments via email.
Since we are storing the full path and filename, you should be able to use any typical code to do this (Outlook Automation, CDO Mail, …). That said, the built-in SendObject cannot handle external attachment though. See: http://www.devhut.net/2018/02/16/ms-access-how-to-send-an-email/ for some potential approaches.
Hey,
I try to use the very nice code for managing attachments published by You.
The published version works fine – great code!
I use your solution in the subform.
However, I need to collect additional information about the history of the attachment being added / removed
and here I encountered a problem …
To track the history of changes in the form and / or subForm, I use published solutions, e.g. (https://answers.microsoft.com/en-us/msoffice/forum/msoffice_access-mso_mobile/making-audit-trial-work-on-subforms/d1a23190-cb9e-4d9c-9e5f-f20a7160d085).
Unfortunately, I can not complete the code to get full information.
Currently registered;
– who (UserName),
– when (datetime),
– name of the subform (FormName) ‘
– action (new, delete)
I do not register the name of the added or delete file and this is important because one record in the main form can be assigned several records in the subform!
also does not register NewValue, (eg filename / path to the file) and OldValue.
Is it possible to modify in this respect?
Could I ask for some help?
BEST REGARDS.
Do you have any suggestions for attaching multiple files at one time?
Thanks
It depends on the context. You can use the FileDialog to perform multiple selections and process each item. You could select a folder, and process each item contained within. It depends on the scenario, but it’s just a question of iterating over a list of items rather than just one item.
Great piece of work, I stumbled across this by chance, just after setting my DB up using the built in attachment function… Its saved me a lot of trouble down the line..
I am struggling with one piece though and was wondering if there a suggestion to get around it..
Its possible to overwrite uploaded files by using the same file name, in my DB the uploaded files are part of the manufacturing history so users being able to unwittily overwrite files is dangerous.
Is there a suggestion on how to either block overwriting with a warning if the file name already exists or re-name the uploaded by adding the by date and time to the file name prior to saving on the server?
I have tried a few approaches but cannot seem to get it working
You can use Dir() to check for the existence of a file and then simply display a MsgBox to the user asking if they wish to proceed, or not?
Hi Daniel,
I’m sure its my lack of talent… I can’t seem to get it to work,
I’ve copied the function from the link and pasted it into the Mod_ExternalFiles Module,
In the locate file sub
If FileExist(sFile) = True Then
MsgBox (“File Name Exist”)
GoTo Error_Handler
End If
But its always popping up the msgbox… even if the files doesn’t exist
sFile is the original file, so it will always exist, you need to test against the destination file. I’d probably do something like
Private Sub cmd_LocateFile_Click() On Error GoTo Error_Handler Dim sFile As String Dim sFolder As String sFile = FSBrowse("", msoFileDialogFilePicker, "All Files (*.*),*.*") If sFile <> "" Then sFolder = Application.CodeProject.path & "\" & sAttachmentFolderName & "\" 'Ensure the Attachment folder exists If FolderExist(sFolder) = False Then MkDir (sFolder) 'Check if the File Already Exists If FileExist(sFolder & GetFileName(sFile)) = True Then MsgBox "The File Already Exists", vbCritical Or vbOKOnly, "Operation Aboarted" GoTo Error_Handler_Exit End If 'Copy the file to the Attachment folder If CopyFile(sFile, sFolder & GetFileName(sFile)) = True Then 'Add this new path to our db Me.FullFileName = sFolder & GetFileName(sFile) Else 'Probably should report something here about the File Copy failing End If End If Error_Handler_Exit: On Error Resume Next Exit Sub Error_Handler: MsgBox "The following error has occured" & vbCrLf & vbCrLf & _ "Error Number: " & Err.Number & vbCrLf & _ "Error Source: " & sModName & "\cmd_LocateFile_Click" & vbCrLf & _ "Error Description: " & Err.Description & _ Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _ , vbOKOnly + vbCritical, "An Error has Occured!" Resume Error_Handler_Exit End SubThat’s worked a treat, thanks very much for your quick responses
How could I rename the file in the above code in this comment when copying to the attachments folder (rename copy not the orig)? Could I just create a Dim sRenamedFile as string and make it equal (“Quote_For_Requsition_Number_” & [txt_Req_Num] & “_” & [txtReq_Desc] & “.*” to be able to rename “Example.pdf” to “Quote_For_Requisition_Number_123456_new computers.pdf” when copying to the Attachments folder? Would I have to now define CopyFile above as (sfile, sFolder & sRenamedFile)? I would change Me.FullFilName = sFolder & sRenamedFile.
This works great but I’d like to be able to rename the file chosen on FileCopy to the Attachments folder (rename the copied file when copying not the original file) and then of course Me.FullName would have the new file name in it. I guess there would have to be a StrNewFileName or something declared? I would probably want to add two fields to the underlying table/form that would pre-populate OnOpen from another currently opened form say fields [Req_Num] and [Req_Desc]. StrNewFileName would be something like (“Quote_For_Requisition_” & [Req_Num] & “_” & [Req_Desc] & “.*”) so that the copied file in the Attachments folder would be re-named ex: “Quote_For_Requisition_123456_New Servers.pdf” if the original file was a pdf. Is that possible with this project’s code (I realize my code is probably erroneous, I’m just trying to guess what it should look like off the top of my head).
I have run into a problem where the FullFileName field can only be a certain length in characters (259 I think from playing around with it). I changed the field in the attachment_server table to be long text (even deleted the field in design view and then created new as long text and I also deleted/re-created the bound field in the server attachment form. However, it still throws The error 53 message from the mod_ExternalFiles if the total length of FullFile name is too long (over 259 characters). Is there anyway this can be fixed so that it will support longer total file name paths? I have searched all over in the code and can’t find anywhere to define the max string length. Thanks.
Hello, This is great thank you very much for posting such a wonderful resource. My, well they’re not attachments I guess, better to call them associated files or referenced files? My associated files are of several types, .documents, .pdf, spreadsheets and images and your system works wonderfully for all of them. It also allows me to make a change to the associated files with no problem as long as the filename remains the same which is an added bonus.
I need to print the associated files in a report. I created a subreport for them which lists them just fine. And, I have code that prints them correctly from the subreport if I put it in the On Current event and manually advance to the report page. But can’t figure out where to put the code to just print automatically when I print the entire report. I was wondering if you had any advice?
With Thanks,
Chris
Hello! Excellent article!
I have the same problem like “Barry Stoker March 8, 2017 at 7:40 am”
but i have set an Reference to Microsoft Office 16.0 Object Library
any idea?
Hi Daniel,
Hope you are doing well. Great article on adding Attachments. Exactly the thing I needed.
I have a question about the “mod_DB_Variables”. There is a static folder location “Attachment”. Would it be possible to make this variable? Like project-specific name or project number? In my table, I added a column “Project” that shows me to which project a certain attachment belongs to. It would be great if this could be project-specific.
Another question about opening the file. Currently, it opens the specific file. Would it also be possible to open the file location instead of the file?
Thanks in advance,
Ruud
Question 1:
You should simply be able to switch ‘Public Const sAttachmentFolderName = “Attachments”‘ to a public variable or even a TempVars, then you could use your form’s current even to set it to you project value that represents the proper foldername.
Question 2:
You can use code like what is found at https://www.devhut.net/vba-extract-the-path-from-a-file-name/ to get the path from the FullFileName field and use the ExecutreFile on that value, or Application.FollowHyperlink, …
Thank you for the great help with all around dilemma
I almost got the Template to work. The link fires in t_attachments_local, bur the BUTTON doesn’t work, at least for me, in the f_Method1_Local:
Hello,
Please, i need an updated code to run this sample on my ms access version 2016
Any ideas?
It can run fine on 2016 or above. What issue are you facing exactly?
Hello
I will contact you from Iran
Please help
How can I attach an image file to a table in the Access database by VBA?
All the best to you
Hi there-
Thx so much for posting this… Very cool feature…
I tested it and it worked fine. Created a new folder in the backend folder on a network drive.
I then added to my database, separate table and form for each, companies, contacts, products and employees so we can store attachments for each.
When I was done and started testing, I realized it was creating the “Attachment” Folder on my desktop instead of the network drive.
I think they only thing I modified was the CopyFile name from CopyFile to CopyFile2 because that name is already in use.
I like that it’s being copied to the network…
Any ideas? Something stupid I did? Thx so much…
No, you are not doing anything wrong and I’m surprised no one has flagged this in all these years. The current code relies on:
but for a properly split setup we need to use the path of the back-end.
So the quick fix is:
sFolder = GetFilePath(GetLinkedTablePath("t_Attachments_Server")) & sAttachmentFolderName & "\"Obviously, change the t_Attachments_Server to the name of one of your tables.
I will work on updating this sample in the coming days.
The sample file for the article has been updated now, so I’d urge you to use that version.
Hello,
This is a great solution that works perfectly for me. The only problem I seem to be having with it is that I’m trying to run it in parallel with Control Application Interface by Isladogs. You see, when a user opens the form, I want the form to show up but for the user to not be able to open the access itself.
I know it’s not specific to the version I’m referring to, because I’ve tried a few different ways to minimize and make it impossible to reopen the actual access window, but every time I do this – it clashes with part of this code. Specifically, I run into a problem with variable msoFileDialogFilePicker, which is now considered “undefined” (the moment I remove the code it starts working fine again).
I understand the code somehow relies on access itself appearing active, but I’m not technically adept enough at VBA to figure out how to alter how this variable is defined to make both these applications work at the same time.
Have you ever tried or know of an alternative way of having the user open their database and not be able to view access itself (but the form(s) appear as an application) that does not clash with this great solution for file attachments.
Thank you in advance for your response.
Sorry, this was my mistake so ignore this comment. I created a new database to try both applications together and forgot to input necessary reference library which I was sure was there.
Newbie mistake. Thank you for this great solution!
Thanks – used this for a school admin that wanted to use the default student tracking template, but wanted attachments on the Students. Works great so far (still in test).