Attachments
By Richard Rost
12 months ago
Access Attachment Data Type: Use, Risks, Alternatives In this Microsoft Access tutorial I will show you how to use the attachment data type, discuss why it is not recommended for most databases, explain the best alternatives to attachments, and demonstrate how to add, remove, and manage attachments in your tables and forms. You will also learn how to reduce database size after removing attachments and why storing file references is a better approach for long-term reliability. Reed from Scottsdale, Arizona (a Silver Member) asks: I have some files and pictures, like resumes and employee photos, that I want to save with my data in Access. I heard you say it's not a good idea to use attachments for this, but I'm not really sure why. Can you explain? MembersIn the extended cut, we will learn how to use VBA to extract all attachments from a Microsoft Access database to a folder, including tagging which record each file belongs to, and I will show you how to programmatically remove all attachments from the database. Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
PrerequisitesLinksRecommended CoursesMember Links
Keywords TechHelp Access, ms access attachment data type, attach files to records, extract attachments vba, attachment alternatives, store file paths in table, database bloat attachments, compact and repair attachments, image storage references, document management access, followhyperlink command, remove attachments, access image display, attachment storage limits
Subscribe to Attachments
Get notifications when this page is updated
Intro In this video, we'll talk about the attachment data type in Microsoft Access, including how to add attachment fields to your tables and forms, upload and manage files as attachments, and the impact attachments have on your database size and performance. I'll explain why using attachments is generally not recommended, demonstrate how to remove them, and go over better alternatives such as storing file paths instead. We'll also cover how to display external images and open linked files directly from your database, as well as important considerations when deciding whether to use attachments for your projects.Transcript Today we're going to talk about attachments in Microsoft Access, the attachment data type. How to use them, why you shouldn't use them, the best alternatives to attachments, how to remove them, how to get them out of your database if you put them in there before when you didn't know better, or you inherited a database from somebody else and you want to fix it.
Today's question comes from Reed in Scottsdale, Arizona, one of my Silver members. Reed says: I have some files and pictures, like resumes and employee photos, that I want to save with my data in Access. I heard you say it's not a good idea to use attachments for this, but I'm not really sure why. Can you explain?
You probably heard me trash talk attachments in previous videos. I just don't like them because a Microsoft Access database is really not the best container for other files. It's a great container for your data, but not for storing binary objects like images and Word documents and all that kind of stuff. That stuff is best left stored in your file system, and you can just store references to them, like the path and file name, in your database.
We'll talk about this in a little bit. Essentially, your nice, clean, small, efficient Access database will get terribly bloated, huge, and slow if you start stuffing all kinds of files in it. Access really wasn't designed for that. They added the attachment data type relatively recently. I think around 2007, maybe 2010. Since then, you may have had the ability to store files in your Access database, but I strongly recommend against it.
Microsoft added that feature to make it easy for beginners to add files to their database, but just because something is easy doesn't mean it's good practice. It's kind of like sending images through email. It's not the most efficient method because your email program has to take that image, convert it into a big, long string of text, and send that whole giant thing as an email, as opposed to a direct file transfer type where you can just send images, maybe through Google Drive or whatever.
Now, by stuffing all these files inside your Access database - which, by the way, has a two gigabyte limit - an Access database can only be two gigabytes in size. If you start adding hundreds of these pictures and documents in there, your database can grow to that max size pretty quickly. All those files have overhead too.
Here, I've got a database without attachments, which is only one point something megabytes, and a database where I've stored all these attachments. You can see the database file grew, and it's larger than the size of all these other files put together. There's some overhead too; with each file, it's got to store additional information.
So attachments in Access - it's bad. It's kind of like when Arnold says, yes, but they were all bad, in True Lies when she asked him if he ever killed anybody. Yes, but they were all bad. Love that movie.
While I wouldn't use attachments in a regular production database that runs your company, if you want to make a small hobbyist database or a supplemental database and store some pictures in there, maybe you want to put together a simple single file that's got some employee photos as attachments, that's fine. I'm going to show you how to use them, but just keep in mind that this is not a good long-term strategy for a proper database.
I get it. A lot of you just use Access for fun and it's not something you're running your business off of, and you want to make a little database to store some pictures. That's not bad for that.
I'm going to open up this database without attachments here. This is just a copy of my TechHelp free template, which you can get off my website if you want to. You'll see there's nothing in here. I didn't put the attachments in this one.
I'll go to my customer table, design view, and add down here a field called attachments. An attachment data field can hold multiple attachment files inside of it. That's why I called it attachments. It's like any kind of mints. Managements or accomplishments. I got a little tin of mints one year for Christmas. It said managements or something. Anyway, find the attachment data type. There's not much to set down here. Save it, close it, open up the customer form, and just for the purposes of class and to save space, I'm going to delete all that stuff.
Let's go to form design and add existing fields, and you'll see attachments down here. Now it opens up into this block of other stuff. Don't worry about this; I'll talk about the other stuff in the extended cut when we talk about programmatically removing all the attachments from the database. Just grab the attachments, and you can close that if it's open. Grab this guy, drop it right about there.
Here's your attachment field. I'm going to move the label on top and move the field down below it like that. You can grab that upper left corner and move them independently. Make this big because you can see a preview. If you put images in here, you'll see the first image in there. Let's do a little format painting here. There we are. Make it nice and pretty. You got attachments.
That's all that you have to do. You add a field to the table, you add the field to the form, a control to the form. Save it, close it, close it, open it. You got this big block here. How do you put stuff in it? Double-click. You'll have the hover. Double-click to add attachments. You can also right-click and you'll see manage attachments. Same thing. Attachments, double-click to open.
If you have a list of them - we don't have any - let's hit add. A little browser window pops up. Pick the files you want to put in there. I'll just take this one small one. Like the Picard Riker, me as Spock, me as Terminator. I'll put a couple different ones in there. All right, hit OK. Hit OK again. There you go.
There's your attachments. You'll see the first one as a preview. Open it up. You can click on this. You can open this. You'll see me as the Terminator. I was having a little fun. Me as Spock, open. I've been playing with AI a lot lately with different things, and so on.
You can save these. You can save all of them if you want to extract them later. To remove one, you just hit remove. It's that simple. Hit OK. Now that's stored in your customer table.
You'll notice also, if I close now, you'll see my database has grown from 1.1 megabytes to now 3.8, because it's got all those files inside of it. If I open it back up, come in here, and I remove the attachments and hit OK, close it, close it, let me hit F5 to refresh. It's still got a bloated database.
Don't forget, after you remove attachments, you have to do compact and repair. That will compact your database and get rid of all that empty space. You'll see it's back down to almost where it was before. There's a little bit of overhead. It's a little bit bigger because I added that attachment field, so that reserves a little more space in there. That overhead is not much, though.
If you want to learn more about compact and repair, go watch this video. It's something you should be doing regularly to your Access databases.
What should you be doing if you care about proper database development? If you just want to work with images, go watch this video. I show you how to properly store references to your images in your database. You can still display them in your forms, you can display them in your reports, but you're only storing the path and file name to the image in your database, so you don't bloat it with the actual image itself.
Watch this video. In fact, I've got an entire Access Imaging Seminar where I teach you all the different ways to work with Access images, including creating a folder on your server where you can pick images from anywhere, like on your desktop, and your database will copy them up to the server and store a link. All kinds of cool stuff in the seminar. I'll put a link to this down below.
As far as other file types go, Excel spreadsheets, PDF files, Word documents, you can store references to those too. Store the path and file name to the file in your database. Using the FollowHyperlink command, you can still click, and it will open up that file. That's how you're supposed to do it.
If you want to keep references to Word documents and so on, go watch this video. In my ABCD database, part five, I build an entire image and document management center. For each customer, you can have all kinds of other documents and images attached, but they're not attachments. They're stored the same way that I just talked about. You store the reference and then you can view the file that way.
Let's say you have a database and you have hundreds of attachments already in it. Either you did this before you knew any better, you inherited a database from somebody else, or you've got your consultant or clients with this problem. You want to extract all of those attachments, but extract them smartly. You want to extract each one, and let's say they're stored in the customer records; you want to extract them so you know what customer they belong to.
In the extended cut, I'm going to show you how to do that. I'm going to teach you how to use VBA to extract the attachments to a folder. Then we'll also be able to tag what record they belong to and all that stuff. That will be in the extended cut for the members.
Silver members and up get access to all of my extended cut videos. Gold members can download these databases that I've built in the TechHelp series. You get the code vault and everybody gets some free training. Come on, get on board, join, and there is lots of free stuff for you.
I also want to take a second to give a shout out to Brian Coleman. Brian tried to reach me by subspace, but his radio was not transmitting. He just wanted to let me know that he searched for Microsoft Access Attachment Data Type on Google and did not find one of my videos. Thank you, Brian, for pointing this out. I've had this on my list to do a video for a long time now. In fact, this original question from Reed is probably two years old. I just haven't bothered because attachments are not something that I use, and I really don't think a lot of people out there use them, so I wasn't going to bother doing a video. But since you did a search and you didn't find me, I'm making a video on it.
This is my goal: if you search for anything Microsoft Access related, any time, and one of my videos doesn't show up at least in the top three results, I want to know about it. Drop me a line, and thank you, Brian. Kaplah.
There you have it. That's attachments. They are easy to use, beginner friendly, but they're not the best solution if you care about long term database performance, reliability, and management. Now you know what they are, how to use them, how to remove them, and what your better alternatives are. Members, I'm going to teach you how to remove them with some VBA, so you can get rid of all of them in one shot in the extended cut.
That's going to do it. That's your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Overview of the Microsoft Access Attachment data type
How to add an Attachment field to a table
Adding an Attachment control to a form
Uploading and saving files as attachments in records
Removing attachments from a record
Effects of attachments on database file size
Using Compact and Repair after removing attachments
Using attachments in hobby vs production databases
Recommended alternatives to storing attachments
Storing file paths instead of attachments
Displaying external images in forms and reports
Opening linked files (images, Word, PDF, Excel) via file path
Limitations and disadvantages of the Attachment data type
COMMERCIAL:
In today's video, we're learning about the attachment data type in Microsoft Access. You'll see step by step how to add attachments to your tables and forms, why using attachments is almost always a bad idea, and the problems they can cause for your database size and performance. I will explain better ways to manage files, like storing paths to images or documents, so your database stays small and efficient, and you'll learn how to remove attachments if you already have them. In today's Extended Cut, I will show members how to use VBA to extract and remove all attachments from your tables in one shot. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What is the primary reason the instructor recommends against using the Attachment data type in Microsoft Access databases?
A. Attachments cannot be displayed in forms or reports
B. Attachments make the database file size grow rapidly and cause performance issues
C. Attachments are not compatible with newer versions of Access
D. Attachments are difficult to add to tables and forms
Q2. According to the video, what is the better alternative to storing files as attachments in your Access database?
A. Store the path and file name to the file in your database instead of the file itself
B. Store files in a separate SQL database
C. Compress files before attaching them
D. Use Excel to store both data and files
Q3. What is a significant limitation of Microsoft Access databases mentioned in the video?
A. They can only have up to 255 fields per table
B. They are limited to two gigabytes in size
C. You cannot store images in any form
D. They only work on Windows XP
Q4. What happens to the Access database size when you add attachments?
A. The database size does not change
B. The database size decreases due to compression
C. The database size increases, often more than the size of the files themselves because of overhead
D. There is only a small temporary change in size
Q5. What feature should you use after removing attachments from your Access database to reclaim disk space?
A. Reindex Tables
B. Compact and Repair Database
C. Encrypt Database
D. Backup Database
Q6. For displaying images in forms and reports without bloating your database, what does the instructor recommend?
A. Store the images in the database as OLE objects
B. Store only the reference (path and filename) to images
C. Store images in text fields as Base64 strings
D. Email images to yourself and keep a record in the database
Q7. What is the primary drawback of using attachments as a long-term solution for file storage in Access?
A. Attachments require internet access to function
B. Attachments cannot handle PDF files
C. Attachments cause database bloat and can quickly hit the Access file size limit
D. Attachments slow down the database design view
Q8. When is it considered acceptable to use attachments in an Access database, based on the video?
A. For small hobbyist databases or single-file simple databases
B. For all business-critical databases
C. For very large collections of files
D. When sharing with SQL Server
Q9. What technique does the instructor suggest for extracting all attachments from an Access database and keeping track of which record they belong to?
A. Manually saving each attachment one by one
B. Using the compact and repair tool
C. Writing VBA code to extract attachments to a folder and record related information
D. Using the Export Wizard
Q10. What is one advantage of storing file references in Access rather than the files themselves?
A. Files are automatically included in Access backups
B. Database size remains small and performance stays optimal
C. Access automatically encrypts referenced files
D. Printing images is easier this way
Q11. Which of the following types of files can you store as references in an Access database, according to the tutorial?
A. Only images like JPEG or PNG
B. Any file type, such as Excel, Word, PDF, by storing the path and filename
C. Only Microsoft Office documents
D. Only files under 1MB in size
Q12. How do you add an Attachment field to a form according to the demonstration?
A. Drag the Attachment field from the field list into the form in design view
B. Use the Image control from the toolbox
C. Type an SQL command to add the field
D. Add it by running a macro
Q13. What do you need to do to view or open an attached file from an Attachment field in a form?
A. Double-click the attachment control or use "Manage Attachments"
B. Right-click the form and choose "Image Viewer"
C. Copy and paste the file from the Windows explorer
D. Use a custom VBA script every time
Q14. What Access command is recommended to open files using the path and file name stored as references in the database?
A. OpenFileDialog
B. FollowHyperlink
C. ShellExecute
D. LaunchFile
Q15. If you remove all attachments from a database but do not perform a Compact and Repair, what will happen to the database file size?
A. The database will automatically shrink back to its original size
B. The file size will remain larger due to unused space
C. The database will be corrupted
D. The file size will decrease below its original size
Answers: 1-B; 2-A; 3-B; 4-C; 5-B; 6-B; 7-C; 8-A; 9-C; 10-B; 11-B; 12-A; 13-A; 14-B; 15-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone is all about the Attachment data type in Microsoft Access. I want to explain how this feature works, why I advise against using it for most applications, what the better alternatives are, and how you can clean up your database if you have already used attachments in the past or inherited a database from someone else that contains them.
This topic comes from a question about storing files and pictures in Access, such as employee photos and resumes. Reed mentioned that he has heard me warn against using attachments but wanted a detailed explanation of why this is the case.
Let me start by sharing my perspective. Over the years, you may have heard me repeatedly say that storing attachments in your Access database is not ideal. Access databases are well suited for storing structured data, but they are not efficient containers for binary files like images, Word documents, PDFs, or spreadsheets. It's always best to store your actual files in the file system and just keep a reference, such as the path and file name, inside your database.
If you store files directly in your Access database using the Attachment data type, you risk bloating your file dramatically. Access databases have a strict maximum file size of two gigabytes, so it doesn't take long before you're up against that limit if you frequently store attachments. What's more, every file you put in the database comes with some overhead, which only accelerates the problem. For example, a small database can grow far beyond the sum of its individual files as soon as you start packing attachments in.
The Attachment data type was introduced around Access 2007 to make it easier for beginners to add files to their database, but just because something is easy does not make it good practice. It's similar to sending pictures via email, which involves converting an image into a string of data so it can be transmitted as a message - not exactly efficient. File transfers or sharing via services like Google Drive work much better because they are designed to handle those types of files.
If you have a production database or plan to run your business on Access, I cannot recommend using attachments. On the other hand, if you are just experimenting, making a fun project, or building a simple database for personal use, it's not terrible to store a few images or files as attachments. Just understand the consequences regarding database size and future growth.
To walk you through how attachments work: I used a copy of my TechHelp free template. I went into the customer table in design view, added a new field called "attachments," and set its data type to Attachment. This field can hold multiple files for each record, which is why I named it in the plural. Once you save your table, you can bring this new attachment field into a form.
When viewing a form, the attachment control shows up as a box. You can place it wherever you need, format it for better appearance, and if you add image files, you'll see a preview inside the control. Adding attachments is straightforward. You double-click the box or right-click to manage attachments, then select and add your desired files.
If you open the Manage Attachments dialog, you can bring in one or several files, including images, documents, and more. From there, you can preview, open, or remove attachments as needed. All these files live directly inside your Access table. However, as soon as you add attachments, you will see your database file jump in size.
If at some point you decide you no longer want these attachments, you can go back into the Attachments dialog, remove each file, and save your changes. Be aware that removing the attachments does not immediately shrink your database file. Access marks the space for deletion, but you must run Compact and Repair to recover that space and reduce the file size. Even after compacting, your file may be slightly larger than before because of the added field and some residual overhead.
If you want more details on Compact and Repair, I have a dedicated lesson on my site that you should check out. It's a good habit to get into with Access databases, as it helps maintain performance and stability.
Now, if you care about best practices and want to keep your database efficient, it's much better to store only the path and file name to your images or documents, not the actual files. You can then display images on forms and reports using this reference method. I have video tutorials that show you step by step how to work with images "the right way" in Access. I even have a complete seminar just on Access imaging, where I cover different approaches, including how to have your database copy files to a central location and manage links to them.
As for non-image files, such as PDFs, Word docs, and Excel files, you can also store just their file paths in your database. You can use commands like FollowHyperlink to open those files directly from Access without storing the actual documents inside your database. Again, I cover all these techniques in my tutorials. For a more advanced example, I walk through building a document management center where you can associate multiple external files with each customer record, all based on this reference method.
But what if you already have a database packed with attachments? Maybe you created it before learning about these caveats, or you inherited it. If you need to extract these attachments to files and associate them with the right records, I have you covered. In the Extended Cut for members, I will show you how to use VBA to pull all those attachments out of your database, saving them to folders and tagging them with the appropriate record details.
If you are a Silver member or higher, you get access to my Extended Cut videos, which go further in depth. Gold members can also download my sample databases and get access to my code vault and more.
On a related note, thank you to Brian Coleman for pointing out that my site was missing a tutorial specifically titled "Microsoft Access Attachment Data Type" in search results. That nudge was helpful and gave me the motivation to finally put this lesson together. If you search for anything Access-related and you can't find me in the top results, let me know!
To sum up, Access attachments are easy to use and might fit some small-scale or hobby uses, but they are not the solution if you want a fast, efficient, and scalable database. Now you know how they work, how to use and remove them, and most importantly, what smarter alternatives exist.
In today's Extended Cut, I will show members how to programmatically extract all attachments using VBA so you can clean your database up in one shot.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Overview of the Microsoft Access Attachment data type
How to add an Attachment field to a table
Adding an Attachment control to a form
Uploading and saving files as attachments in records
Removing attachments from a record
Effects of attachments on database file size
Using Compact and Repair after removing attachments
Using attachments in hobby vs production databases
Recommended alternatives to storing attachments
Storing file paths instead of attachments
Displaying external images in forms and reports
Opening linked files (images, Word, PDF, Excel) via file path
Limitations and disadvantages of the Attachment data type |
