- Access allows you to save attachments or just paths, as needed.
- Using attachment fields makes document management and viewing easier.
- Saving only paths keeps the database light, ideal for large volumes.
- The functionality is suitable for users without advanced programming knowledge.
Have you ever wondered how to organize documents, images, or linked files directly from a database without the hassle of writing code? Microsoft Access is one of the most powerful and user-friendly options for storing and managing attachments or document paths, allowing you to centralize all your information efficiently.
In this article, I'll explain clearly and simply how to create an Access database that stores paths or attachments , its advantages, the most important steps, and the technical details you need to know to make your document management robust, fast, and easy to maintain. Get ready to discover everything you need to know, from the most basic concepts to advanced features, incorporating the best methods and practical tips.
What is a database in Access and what is it used for?
An Access database is a system for organizing, classifying, and protecting a wide variety of information: from customer and product data to electronic documents, invoices, and even photographs. Access not only allows you to create tables and store data, but also offers visual tools for queries, reports, and forms, thus facilitating data entry, searching, and analysis.
The most interesting feature is that Access includes specific functions for attaching files directly to records. This is especially useful if you work with digitized documents (invoices, contracts, images, etc.), or if you simply need to maintain an organized list of file locations on your disk using paths.
With Access you can:
- Create and customize tables for any need (inventory, customers, invoice history, etc.).
- Attach one or more files for each record easily.
- Linking files using paths to keep the database light and agile.
- View, modify and manage documents or images from forms and queries.
Attachment fields and paths: Which option should I choose?
Access allows you to save files using its Attachment field type , or you can directly store the file path (its location on the system), which can be done using a text field. Which option is better for you?
If you want to save the file to the database (for example, a PDF invoice or an image), the attachment field is perfect: it keeps everything centralized and allows you to access files without depending on the original folder. Furthermore, Access can automatically compress files according to their format, helping to save space.
On the other hand, if you don't want the database to grow too large , or you prefer to avoid duplicates by only storing the file location, simply add a text field and save the path there (for example, 'C:\Invoices\2024\invoice124.pdf'). This way, you open the document from its original folder, and the database remains much smaller.
What is the main drawback of each system? Storing many attachments can make the database too large and slow, while linking paths requires that the files not be moved from their folder, otherwise the link would be lost. Therefore, depending on your needs, you can combine both methods or select the one that best suits you.
Advantages of using attachment fields in Access
The Attachment field type has superseded older techniques like OLE (Object Linking and Embedding), which were inefficient and space-consuming. Its main advantages include:
- Easy to add files natively, without programming anything.
- Compatibility with numerous formats: images (JPG, PNG, GIF, BMP), Office documents, zip files, among others.
- No additional software needed to view most attachments, unlike the old OLE.
- Simplified management: With a double click you can add, delete or edit files linked to each record.
In addition, you can attach multiple files to a single record (for example, different documents from the same client), manage them from forms, and save or export them as needed. The maximum size of each attachment is 256 MB, and the total database can reach 2 GB.
How to create a database in Access with paths or attached files?
The creation process is very simple and can be adapted whether you want to save files within the database or just need to save paths. Here are the general steps:
1. Create a new database or open an existing one
Open Microsoft Access and select "New Desktop Database". Give it a descriptive name and save the .accdb file in your preferred folder. If you already have a database created, simply open it from the "File > Open" menu.
2. Create or modify a table
You can do this from the "Create > Table" tab or choose an existing one. Tables will be the core of your database: for example, "Customers," "Invoices," or "Documents."
3. Add an attachment field
To save files directly, you need a special field. Do it like this:
- Open the table in Datasheet View and locate the "Add New Field" column. Give it a name (for example, "Attachment") and select "Attachment" as the data type.
- If you're working in Design View, add a new field, name it, and choose Attachment as the type.
Save the changes. Remember that once you assign the Attachment type, you won't be able to change it later (although you can delete the field if you made a mistake).
To store file paths
Simply add a "Short Text" or "Long Text" field and save the full address of the file or folder there. This way, you can open documents from their actual location by double-clicking or using a custom button.
How to attach files to Access records
Once you have your attachment field ready, adding documents is very easy:
- Open the table in Datasheet view.
- Double-click the cell corresponding to the attachment field.
- A window called "Attachments" will open. Click "Add."
- Find the file on your computer (you can select multiple files at once) and click “Open.”
- Finally, click “Accept” to add the documents to the registry.
You can repeat this process to add more files to the same record, delete them, or edit them from the same window. When files are attached, Access will display a small paperclip icon in the cell to indicate that there are associated documents.
Manage and view attachments from forms
One of the great advantages of Access is that you can extend the functionality of your tables through custom forms . This makes file management much easier.
To display and handle attachments in a form:
- Edit the form in “Design View”.
- From the “Design” tab, use “Add Existing Fields” and drag the attachment field to the form.
- Place the control where you prefer and adjust the properties if needed.
From the form, you can open, add, or delete attachments in a visual and intuitive way. If the record contains multiple images or documents, you can easily navigate between them using the arrows on the control.
Save, edit, and delete attachments
Access also allows you to easily modify or extract saved files:
- Save files to another location: Open the attachment window, select the file, and choose "Save As." Choose the folder where you'll copy it.
- Edit files: Double-click on the attached document and, if you have the compatible program installed (Word, Excel, etc.), you can edit it normally. When you save your changes and return to Access, you'll be asked if you want to keep the modified version.
- Delete attachments: In the attachment window, select the document and click “Remove.”
Remember that Access temporarily stores edited files in the Windows temporary folder until you decide to save them to the database.
Advantages of managing attachments from forms and reports
By integrating attachment fields into forms and reports, you can:
- View images directly while browsing the records.
- Access and manipulate documents from a single panel, without having to search for them manually.
- Add or remove files easily without opening the main table.
Please note that reports only allow you to view or save attachments , but not edit them, as they are read-only.
Supported file formats and limitations
Access can handle most common formats used in daily work:
- Images: BMP, JPEG (JPG, JPE), GIF, PNG, TIFF, ICON, WMF, EMF, among others.
- Documents: Word, Excel, PowerPoint, ZIP files, text files (.txt), XML.
However, Access blocks certain file formats for security reasons (for example, executables, scripts, or files from older versions of Access). Before attempting to attach files, review the official list of blocked formats in the Access help documentation to avoid unnecessary errors.
How do I compress attachments in Access?
Access automatically compresses attachments unless they are already compressed in their native format. For example, JPEG images , GIFs, or ZIP files will not be recompressed, while BMP, TIFF, or uncompressed documents will be, thus helping to optimize the space used in the database.
This compression system allows you to handle large volumes of documents without excessive database growth, although it is recommended not to exceed the maximum limit of 2 GB.
Working with paths: linking external files
In many cases, it's most practical to link the files to the database rather than attach them to the location where they're stored. To do this:
- Create a text field where you will store the full path (for example, D:\Documents\Invoice_A123.pdf).
- Add the links manually or using a form that includes a field for the route.
- In forms, you can add a button with a macro to open the file using the “Link to Hyperlink” command.
The main advantage is that the database size doesn't increase , although you will lose the reference if the file is moved or deleted from its original location. This method is preferred for users who manage large volumes of documents and don't want to overload Access.
Passionate writer about the world of bytes and technology in general. I love sharing my knowledge through writing, and that's what I'll do on this blog, show you all the most interesting things about gadgets, software, hardware, tech trends, and more. My goal is to help you navigate the digital world in a simple and entertaining way.