Working with Attachment data type in Microsoft Access | Expert Zone (2022)

By

Jayaram Krishnaswamy

Working with Attachment data type in Microsoft Access | Expert Zone (1)

In MS Access accdb Microsoft introduced a new data type called attachments. Many types of files can be stored in a column which has the attachment data type. Someone can add bitmap files, jpg files, sound files, word documents, etc. to a column without increasing the size of the database.

About this article: We are publishing this article by Jayaram Krishnaswamy here as an added extra to our Access file repair software AccessFIX. We hope you will like this excelent piece about the attachement field type in Microsoft Access.

However, these objects will be run by the appropriate executable programs for these attachments when they are called to do so. This goes even further such that you can attach multiple, different types of attachments to the same record. This allows for the storage of the information about an employee including his picture, his resume, and any other necessary information all in one column. The Access project which is now supported again in Access accdb does not support the attachment data type since the underlying database does not.

This comprehensive tutorial shows an example of creating a table with a column which has the new data type, attachments. It also shows how you may populate the table and view the contents of the table. It goes further and shows you how to create a report based on this table and how the report can be viewed effectively to display or render its contents.

Attachments in Access

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

Some background information

Attachments in Access accdb replace the OLEDB data type of the older versions. However the OLEDB data type is continued for backward compatibility. Access accdb, 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 accdb 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.

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

Figure 1

Working with Attachment data type in Microsoft Access | Expert Zone (2)

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 accdb 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

Working with Attachment data type in Microsoft Access | Expert Zone (3)

Figure 3

Working with Attachment data type in Microsoft Access | Expert Zone (4)

(Video) Microsoft Access 2016: Table Attachment Fields

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

Working with Attachment data type in Microsoft Access | Expert Zone (5)

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

Figure 5

Working with Attachment data type in Microsoft Access | Expert Zone (6)

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

Working with Attachment data type in Microsoft Access | Expert Zone (7)

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

Working with Attachment data type in Microsoft Access | Expert Zone (8)

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

Working with Attachment data type in Microsoft Access | Expert Zone (9)

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

Working with Attachment data type in Microsoft Access | Expert Zone (10)

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

(Video) Microsoft Access 2010 - Using The Correct Data Types - Tutorial

Working with Attachment data type in Microsoft Access | Expert Zone (11)

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

Figure 11

Working with Attachment data type in Microsoft Access | Expert Zone (12)

Creating a Report using the table

Some of the steps in creating a report will be abbreviated. This next screen shown in Figure 12 starts off with a Report Wizard which uses the DesignAttach Table. It shows all the available fields in the table, DesignAttach . Notice the extra fields added to support the attachments.

Figure 12

Working with Attachment data type in Microsoft Access | Expert Zone (13)

In this screen all fields have been transferred to the right (Selected Fields :) using the >> button as shown Figure 13.

Figure 13

Working with Attachment data type in Microsoft Access | Expert Zone (14)

This next screen shown in Figure 14 is another step of the Report Wizard where you may include grouping information. No changes were made here.

Figure 14

Working with Attachment data type in Microsoft Access | Expert Zone (15)

This next screen shown in Figure 15 is the Report Wizard sort order configuration step. Again only the EmpName is designed to be sorted in ascending order.

Figure 15

Working with Attachment data type in Microsoft Access | Expert Zone (16)

Figure 16 shows the next step of the Report Wizard where the layout design is chosen. The columnar design is selected.

Figure 16

Working with Attachment data type in Microsoft Access | Expert Zone (17)

In Figure 17 a style for the report is chosen. Access accdb has a few more new styles in this step.

Figure 17

(Video) Microsoft Access TechHelp Quick Queries #5 - VBA, OOP, Secure Back End, Receive Email, Lots More!

Working with Attachment data type in Microsoft Access | Expert Zone (18)

In the window shown in Figure 18, the report needs to be provided with a title which can be different from the default which comes up. In the example the default radio button choice is accepted.

Figure 18

Working with Attachment data type in Microsoft Access | Expert Zone (19)

When you click the Finish button the report will be created and will be, as was specified, opened in preview mode.

Displaying the Report

When you right click the report, Show Attachments , you can choose the mode in which you want it to be displayed: Open, Layout, Design, and Print Preview as shown in Figure 19.

Figure 19

Working with Attachment data type in Microsoft Access | Expert Zone (20)

Design view of the report

In the design view you can see how the various controls are placed. In this view you will be able to access the properties window of all the sections and controls, make changes, or attach code or macros to events etc. In fact, the default design view of the report occupies such a large area, that it was drastically altered by making all objects occupy a smaller area than can be shown in a single, smaller screen as shown in Figure 20. Each attachment carries with it several pieces of attachment information.

Figure 20

Working with Attachment data type in Microsoft Access | Expert Zone (21)

Layout view of the report

For each attachment in a row, a layout file block section is generated. For the Employee, 'Jay' three such blocks, one each corresponding to the attachment will be present. If you are using the report for the first time you may get somewhat confused. A partial picture of the report in layout is shown in Figure 21. There are 3 such items for the employee, 'Jay' and only one for employee, 'Tom'. Figure 21 only shows a part of the first two blocks for the attachments for employee, 'Jay'

Figure 21

Working with Attachment data type in Microsoft Access | Expert Zone (22)

Report Print Preview

This will vary depending on how the report is configured in the design. For attachments, it would just show icons for the attachments if they exist. In the report considered in this tutorial, since the design was drastically altered, objects get displaced in the print preview as shown in Figure 22. You may notice that the attachment related icons are drastically displaced and shown on top of other fields.

Figure 22

Working with Attachment data type in Microsoft Access | Expert Zone (23)

Report Open

When you pick Open in the drop-down menu for the report (Figure 19), the window shown in Figure 23 will be displayed: It is similar to the layout.

Figure 23

Working with Attachment data type in Microsoft Access | Expert Zone (24)

When you click on the EmpResumePhoto control’s box as shown in Figure 24, you will notice a small navigational control for the attachments pop up as a floating window. Here you can choose the desired file from the three for the employee, 'Jay'.

(Video) Microsoft Access Imaging Seminar - Introduction

Figure 24

Working with Attachment data type in Microsoft Access | Expert Zone (25)

If you click on the attachment icon (paper clip), you will open up the attachment window as shown in Figure 25.
Figure 25

Working with Attachment data type in Microsoft Access | Expert Zone (26)

Here you may select the file you want to view and click on the View button. This pops up an executable program which is supposed to work with this type of file extension. As shown in Figure 26, for the mp4 music file, the Y!Music Jukebox 2.0 application opens. This Access accdb program is running on a Windows XP Multi Media OS.
Figure 26

Working with Attachment data type in Microsoft Access | Expert Zone (27)

For the next item on the attachment a different program will execute the item as shown in Figure 27 for the picture file. Microsoft Word will start when you click on the third attachment (not shown).

Figure 27

Working with Attachment data type in Microsoft Access | Expert Zone (28)

Programmatic access to attachments

The new control called Attachment , shown in the design view of the report in Figure 28, is a new object. You can drag and drop this control on to a design like any other control.

Figure 28

Working with Attachment data type in Microsoft Access | Expert Zone (29)

Once on the form you can use the control programmatically as shown in Figure 29. You may refer to the Attachment object as shown here where Attachment0 is an instantiation of the Attachment object. You can write code to its properties, methods and the events.

Figure 29

Working with Attachment data type in Microsoft Access | Expert Zone (30)

Summary

The attachment data type is another feature of Access accdb that adds zing to this vintage product. The attachment control and attachment object give code access to this new data type. In relation to the attachment data type, the exporting and importing of files into Access accdb and the replication of them still needs to be explored. With multiple attachments to a single record the design page can take up so much space it is hard to keep track of all the controls even with a large monitor. Although this tutorial has used a beta version of MS Access accdb there should not be any significant differences while using the release version.

Working with Attachment data type in Microsoft Access | Expert Zone (31) Jayaram Krishnaswamy

Dr. Jay Krishnaswamy, a graduate of the IISC Bangalore, writes extensively on database and web related topics to several web sites.

Before working in the IT industry as a MCT and a Siebel consultant he taught and researched at several post-graduate institutions and national laboratories in India, Japan, Australia, Netherlands, Brazil , Canada, and USA.

Jayaram Krishnaswamy is a US based freelance technical writer for several web sites on database and web related articles. He has recently completed the Beginners Guide to SQL Server Integration Services Using Visual Studio 2005 that will be available in December 2007.

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005 provides you with the basic knowledge that you should have before you move on to more advanced ETL [Extraction, Transformation, and Loading]. The book will also provide you with a comprehensive description of the many designer windows that you may encounter while working with the designer. This guide provides the building blocks describing each block by way of an example as well as describing the nuts and bolts that bind the blocks. A majority of SSIS tasks are covered in this book and they are described fully in the summary of table contents section. You start building packages right from Chapter 2 and continue on to Chapter 20 gathering and building upon your knowledge in each step.

(Video) 16. Ms-Access - Form Object

FAQs

What is the Attachment data type in Access? ›

You can attach files only to databases that you create in Access and that use the . accdb file format. You cannot share attachments between a Access (. accdb) database and a database in the earlier (. mdb) file format.

How do you use an Attachment in Access? ›

Microsoft Access 2010 Tables: Attachment Field to ... - YouTube

What is the size of Attachment data type? ›

Individual files cannot exceed 256 megabytes in size.

How do you name an attachment field in Access? ›

Microsoft Access 2016: Table Attachment Fields - YouTube

How do you make an attachment? ›

How to Attach a Document to an E-mail - YouTube

How do I view attachments in Access form? ›

25. Learn Microsoft Access 2016: How To Use Attachments On A Form

Can you attach a PDF file to Access database? ›

Although many people often use Microsoft Access to store text information, you can add files to a database as well. Access makes this possible by defining a special attachment field that can hold complex data. After you add an attachment field to an Access table, you can import PDF files into any of the table's rows.

How do you label attachments in a report? ›

In the body of your report, the first time you reference a document, you will want to create a footnote with a full citation. Then add "See Attachment 1" or whatever the number. Thereafter, if you mention the same document in the text you can add a parenthetical (Attachment 1), or whatever the number.

What are the data types of MS Access? ›

Data types for Access desktop databases
Data TypeUsageSize
NumberNumeric data.1, 2, 4, 8, or 16 bytes.
Date/TimeDates and times.8 bytes.
CurrencyMonetary data, stored with 4 decimal places of precision.8 bytes.
AutoNumberUnique value generated by Access for each new record.4 bytes (16 bytes for ReplicationID).
8 more rows

Which is not a valid data type in MS Access? ›

The only thing that is not a valid data type is 'picture' in MS Access.

What is OLE object data type? ›

OLE SafeArrays and OLE Variants are complex data types which are passed into Object COBOL as instances of Object COBOL classes OLESafeArray and OLEVariant. The other data types are converted directly into equivalent COBOL data types.

Can you attach a PDF file to Access database? ›

Although many people often use Microsoft Access to store text information, you can add files to a database as well. Access makes this possible by defining a special attachment field that can hold complex data. After you add an attachment field to an Access table, you can import PDF files into any of the table's rows.

What is the default file extension of an Access file? ›

By default, the file is given the ". accdb" extension — it is created in the Access . accdb file format for your version and is not readable by earlier versions of Access. You have the option of creating files in either the Access 2000 format or the Access 2002-2003 format (both with the extension ".

How do you store images in Access? ›

Access A to Z: Storing and displaying images in your database - YouTube

Videos

1. Chapter 3 Working with MS Access 2010 | Part 2 | Class 8 Computer | Creating Tables in MS Access
(Tejpal Maurya)
2. Widow / Orphan Protection for Records in Microsoft Access - Prevent Data With Missing Relationships
(Computer Learning Zone)
3. Microsoft Access TechHelp Sorted Combo Box
(Computer Learning Zone)
4. Hyperlink Fields in Microsoft Access and Why You Should NOT Use Them
(Computer Learning Zone)
5. MS ACCESS INTRODUCTION TELUGU/ANGRAU/Appsc group 2 computer test// CREATING&SAVING A DATA BASE
(Aditya.M Tutorials)
6. Use File Dialog, Attach Multiple Files to Multiple Recipients: MS Access
(T Golden Eye)

You might also like

Latest Posts

Article information

Author: Tuan Roob DDS

Last Updated: 09/19/2022

Views: 6132

Rating: 4.1 / 5 (62 voted)

Reviews: 93% of readers found this page helpful

Author information

Name: Tuan Roob DDS

Birthday: 1999-11-20

Address: Suite 592 642 Pfannerstill Island, South Keila, LA 74970-3076

Phone: +9617721773649

Job: Marketing Producer

Hobby: Skydiving, Flag Football, Knitting, Running, Lego building, Hunting, Juggling

Introduction: My name is Tuan Roob DDS, I am a friendly, good, energetic, faithful, fantastic, gentle, enchanting person who loves writing and wants to share my knowledge and understanding with you.