Working with Attachment data type in Microsoft Access 2007
page 2 of 6
by Jayaram Krishnaswamy
Average Rating: 
Views (Total / Last 10 Days): 52398/ 96

Attachments in Access 2007

This step-by-step tutorial shows you how to work with the new data type, attachments, in MS Access 2007.

Some background information

Attachments in Access 2007 replace the OLEDB data type of the older versions. However the OLEDB data type is continued for backward compatibility. Access 2007, of course, only allows the creation of the new data type.

One of the rules of relational database design is that columns can store only a single value, a reason why first name and last name are stored in separate columns. Attachments seem to break the rule since it can store multiple pieces of data. It appears that a lot of work has gone into Access 2007 not to break relational data design and yet store multiple items by working behind the scenes using the System tables effectively.

Important attachment related information

·         The maximum size of an attached data piece cannot be larger than 256Mb

·         Adding, deleting, and editing of the attached items is only possible through an Attachments dialog box.

·         In the design of Forms and Reports there is new item in the toolbox: the Attachment control (looks like a paper clip). This control may be used at design time.

·         Editing an attachment is possible if the program used for creating the attachment is available on the local computer. The program will edit the attachment and when it is saved the attachment gets saved to its own field.

·         Access will compress uncompressed files in the attachments before storing them.

·         Attachments may originate from any location on the disk drive or the network.

·         VBA can be used to work with attachments programmatically using the new Attachment Object. It has several properties and methods, and it supports events.

Creating a table with an attachment column

A table named DesignAttach will be created in a blank MS Access 2007 database application called Attach07. It is created with a default table, Table1 as shown in Figure 1.

Figure 1

You can create a new table from the Create tab on the ribbon by clicking on the Table Icon on the far left. This opens a table in datasheet view, very much like Table1 above.  You can save the table by right clicking the Table1 tab above the datasheet view and choosing Save. You will be asked to give a name to the table. In this example, DesignAttach was given as the name of the table.

From the View menu item you can change to the design view as shown Figure 2. The ID field was changed to EmployeeID with the data type, AutoNumber. For the second column, the field was named EmpName with the data type set to Text.  In the field properties section you can set the Field size and other field attributes as shown. In Access 2007 you will immediately see the property sheet of the table on the right. This is presented as a separate screen shot in Figure 3.

Figure 2

Figure 3

A few other fields have been added to our example as shown In Figure 4. The last one being EmpResumePhoto field with the data type, Attachment. You can pick the data type from the drop-down in any row of the Data Type column in the design. A caption of ResumeWithPhoto was added to the field. The design view now shows all the columns in the table.

Figure 4


When you try to close the design view you will be asked to save design changes as in Figure 5.

Figure 5

Populating the first row of the table in the datasheet

The data sheet view of the DesignAttach table now appears ribbon with the table name highlighted when you pick to see the data sheet view from the View menu on the as shown in Figure 6. The column name given to the column with the data type Attachment has been replaced by a paperclip icon.

Figure 6


To fill in some example data, enter the EmpName, and move over to EmpLastName and enter Last name. Repeat this process for EmpPhone and EmpHireDate. You do not enter into EmployeeID as it is automatically incremented. This is all very similar to what you would have done with earlier versions.

Now double click the row under the attachment field to open the Attachments window which is shown next in Figure 7, together with the table view.

Figure 7


Adding items to the column with Attachment data type

As mentioned in the beginning anything to do with attachments has to be carried out by bringing up the Attachments window shown in Figure 8. Now click on the Add... button which opens up an Open file / folder browsing window where you can go through the machine and networked computers to choose items to be added to the column. In this example the Overlooking Rio.jpg file was added. To add more items to the attachments, click the Add... button again. With two more clicks of the Add... button, a word document, Tophost_Oct29-2006.doc and a music file, 04 Brahms Double-III-Vivace Ma N.m4a were added (Files with M4a extension are audio files with Apple’s lossless compression). Some of these files were on the C–drive and some on a network folder.

Figure 8


When you click on the OK button after adding these files, they become part of the column for the Employee with ID=1 as shown in Figure 9. As you can see the attachment column shows that it has 3 items contained in it.

Figure 9


Similarly a second employee was added with just one attachment (this screen shot was before adding the item). If you right click the attachment column you can get a contextual drop-down from which you can click on Manage Attachments... link as shown Figure 10 to open to open the Attachment window.

Figure 10


Of course each time you make changes you will be asked to save the changes as in Figure 11.

Figure 11


View Entire Article

User Comments

Title: Multiple Attachments   
Name: RH
Date: 2012-09-27 12:27:50 PM
Have you figured out a method to keep a report from replicating entries for each attachment? If I have three attachments, like in this article, it creates three instances of the same information.
Title: Feedback   
Name: CH
Date: 2012-09-17 8:30:01 PM
For some reason, not one of the screenshot pictures are displaying, missing file or directory not found.
Title: Saving Attachment links to a separate field as a hyperlink   
Name: Michael Bailey
Date: 2012-08-21 10:54:12 AM
Is it possible to save the link data for an attachment to a separate field as a hyperlink? I am writing a stamp collection database that uses an attachment field to store and view pictures of the stamps, but I also need to be able to store the file locations of the images in a separate field as a hyperlink
Title: How to print attached pdf/word documents   
Name: Torstein Krogh
Date: 2011-09-12 5:34:12 AM
How to print attached pdf/word documents from Access 2010?
Title: Delete attachments   
Name: dafrak
Date: 2011-04-03 9:59:44 AM
Something like this:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsPics As DAO.Recordset

Set db = DBEngine(0)(0)

Set rs = db.OpenRecordset("picsTable")

If rs.RecordCount > 0 Then
Set rsPics = rs.Fields("picsFormFieldName").Value
While Not rsPics.EOF
End If

Set db = Nothing

Title: Delete Attachments   
Name: twl
Date: 2011-01-25 6:03:43 PM
I have a table that stores the attachments. I used a delete query to delete all records. When I compact and repair DB the size does not shrink.

Any ideas. I need to automate the clearing of attachemnts.
Title: Feedback   
Name: ES
Date: 2010-10-01 12:11:38 AM
Fabulous article - have book marked you!
Name: sathisBoy
Date: 2010-09-16 12:48:58 AM
cn i knw hw to view the attachment in
and cn v save exe file as attachment??
Title: Eng   
Name: Qutaiba
Date: 2010-07-26 6:22:55 AM
i have problem in access VBA code
this code:Attachment212.SetFocus
rst.Fields("pp").Value = Attachment212.AttachmentCount
i recived erorr how i fix it
Title: Working with Attachment data type in Microsoft Access 2007   
Date: 2010-07-23 3:24:57 PM
I would like to know how to get the attachment file on to a form instead of a report - is this possible?
Title: Multiple attachments only outpus one image   
Name: Chris
Date: 2010-04-01 3:32:29 PM
I've got several images attached to a record. The report prints out a copy of the record for each image, but only displays the first image. Suggestions?
Title: Dr.   
Name: Jayaram Krishnaswamy
Date: 2009-07-30 8:13:28 AM
Presently I do not have this product on my computer, but I will try to get back to you in a couple of days. In the mean time the best bet is to search Microsoft Office site.
Title: Dr.   
Name: Khalid Amer
Date: 2009-07-30 7:46:28 AM
I also forgot to include my email.
Title: Dr.   
Name: Khalid Amer
Date: 2009-07-30 7:44:48 AM
How can you programmatically copy one attachment from one table to another?
Title: IDIOT   
Name: ARTHUR hill
Date: 2009-07-23 2:50:41 PM

I forgot to include my email with last question.
Title: CPA   
Name: Arthur Hill
Date: 2009-07-23 2:49:42 PM
Do I understand correctly that the only way to access the properties of an Attachment type field in a table record is to use a Attachment control? I want to programatically add attachments to records by iterating thru the recordset and adding an attachment, the file name of which is based on the ID field of the current record. I do not want the records displayed on the form.

Thanks for your assistance.

Arthur Hill, CPA, MBA, MCSD, MCDBA
Title: Working with Attachment data type in Microsoft Access 2007   
Name: Jayaram Krishnaswamy
Date: 2009-07-15 12:27:03 PM

Thanks for the kind comments. Looks like it was not all that comprehensive! It's sometime ago I wrote this and I don't have Office 2007 on my PC. It may be possible to set up a hyperlink to get the actual resume. Next time I will take a look at this.

Title: Working with Attachment data type in Microsoft Access 2007   
Name: Mike Kovatch
Date: 2009-07-15 12:12:28 PM
Very good and comprehensive article. Have you figured out a way to output the entire attachment as part of the report (not just the icon representing the attachment)?
As an example, let's say the report was an interview sheet that had 10 questions with blank areas to takes note depending on their response. The report would in include a copy of the applicant's actual resume. Is this possible?
- Mike

Product Spotlight
Product Spotlight 

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2024  |  Page Processed at 2024-06-18 5:44:34 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search