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