databasedev.co.uk - database solutions and downloads for microsoft access

Attachment feature in Microsoft Office Access 2007

The Attachment feature is used in Microsoft Office Access 2007 to add one or more pieces of data to the records in your database. Attachments are used to store several files in a single field. For example, you can store a resume and a photo for each contact in your database.

Previous versions used Object Linking and Embedding (OLE) to store images and documents but attachments store data more efficiently. The attachments save the files in their native formats hence there is no need to install additional software in order to view the attachments from your database.

Just like in the case of multivalued fields in Microsoft Access 2007, when attaching multiple files to a record in your database, Access creates one or more system tables and uses them to normalize your data.

General Guidelines before using attachments

You can add files only to Microsoft Office Access 2007 databases (.accdb file format) and you cannot share attachments between Access 2007 files and older versions.

You must create a field in the database and set it to Attachments data type. Once created, you cannot change the type.

You can store multiple records in a single file (resume, photo, etc) but you can attach a maximum of 2GB of data (256 MB/file maximum).

Use the Attachment dialog box to add, edit and manage your attachments. In a report, you can only save the attached files to another location but when using forms and tables you can edit , remove or save your attachments.

You can edit and save the attached files on your computer provided you have the software to do that.

Using Attachments with Tables

Add an Attachment Field to a Table

Open the table in Datasheet view and click the first available blank column.

Figure 1 Table opened in Datasheet view

Click the Datasheet tab and in the Data Type & Formatting group, click the arrow next to Data Type and choose Attachment.

Figure 2 Select the data type

Access 2007 now sets the type of the field to attachment.

Figure 3 The new field we just created

Don't forget to save the table.

You can also add an Attachment filed in Design view. In the Navigation Pane, right click the table and choose Design View.

Figure 4 Click to open design view

In the Field Name column, write the name of the field which will contain the attachments and under Data Type choose Attachment.

Figure 5 Add the field name and the data type

Save the changes and then go back to Datasheet View.

Figure 6 Go back to datasheet view

Attach files to Tables

In Datasheet view double click the Attachment field where you want to add a file. This will open the Attachments dialog box.

Figure 7 Double click the field to open the Attachments dialog box

Now click on Add… and a browse dialog box (called Choose File) will appear. Navigate to the file’s location , select the file and click Open.

Figure 8 Choose the file and click Open

The file will now be shown in the Attachments dialog box. Click OK.

Figure 9 Click Ok to add the file to the record

In the data base you will now see that the record has one file attached.

Figure 10 The attachment as it appears in the database

Open the Attachments from a Table

Go to Datasheet view and double click the cell in the attachment field. It will open the Attachment dialog box. Select the file and click Open or just double click the file. It will launch the application in which the file has been created.

Figure 11 Open the file

Save Changes to an attached file

Use the software in which the file has been created to edit the file. Save the changed and close the parent software. Return to the Attachments dialog box and click OK. The following message will appear.

Figure 12 Make the changes in the database as well

Click Yes to make the changes in the database as well.

Using Attachments with Forms and Reports

When using attachments with forms or reports you need to create an attachment control. The control displays the icon that corresponds to the file type and allows you to browse any attached files and view them.

Note: When using attachments with reports, you can only save an attachment to another location because report are read-only by design.

Add the attachment control to a form or report

In the Navigation Pane right click the form or report and click Design View.

Figure 13 Open the form/report in design view

Go to the Design tab, and in the tools Group click Add Existing Fields.

Figure 14 Add existing fields

Drag the attachment field (called Documents in our database) onto the form/report and place it where you want.

Figure 15 Drag the field onto the form/report

Figure 16 The field placed in the form (design view)

Save the changes and go to Form/Report View to open the form/report for viewing.

Manage the attachments in a form

After you added the control, you can edit, add, remove or save attachments in that form.

To attach a file, open the form, select the attachment control and a mini toolbar will appear. Click the paperclip icon and the Attachment dialog box will appear. Now add a file just like you did in a Table.

To scroll through the attached files in a form or report, open the form/report , navigate to the record that contains the attachment, click the image and the tool bar will appear again. Use the back and forward arrows to navigate the attachments.

Save Attached files to other locations

The steps are similar for tables, forms and reports. Open the table/form/report and then open the Attachment dialog box. Select the file and click on Save As. If you want to save all the attached files , click Save All. Use the browsing dialog box to save the file/files.

Figure 17 Save one or all attachments

Remove attached files

You can remove attached files from tables or form. Open the table/form, double click the attachment field and in the Attachment dialog box, select the file, then click Remove.