Hands on: Getting attached to Access 2007

As discussed last time, I’m going to use Access 2007 from now on to illustrate this column, unless the esteemed readership tells me otherwise.

So, until the votes are all in and counted, we’ll take a look at the new version.

I’m not convinced by the usability of the new user interface for Office (nor by the multi-valued data types that have appeared in 2007) but that doesn’t mean there are no good features - and one I’d deem good is the new field type called Attachments.

As the name suggests, this data type allows you to add an attachment to a row in a table. In this case, the word ‘attachment’ is being used in the same sense as adding a file to an email.

If you add a field of this type to a table you can store documents, images and many other types of file in it. You can also attach more than one file to a single record in the table. The easiest way to understand it is, of course, to try it out.

Create a new table, flip to editing the structure and add a field called Attach.

Save the table structure and doubleclick on the Attach field. An Attachments dialogue box opens up.
This dialogue box is as easy to use as its structure suggests. You can use the buttons to add and remove attachments, open them, save them to a new location (outside the database) and so on. Double-clicking one of the attachments opens it.

An important question at this point is ‘Where exactly is this file now?’ During the attachment process you use another dialogue box to navigate to the file in question. Does Access simply leave the file there and point to it or does it leave a copy in the file system and embed another copy into the Access database? This is an important question because if Access simply points to the file, as soon as you send the Access database to someone else, the attachments will be unreachable (unless they happened to be on a shared drive).

The answer is that the original is left in the file system and a copy is placed in the Access database as the attachment. So the attachments do travel with the Access database. The recipient can, if they wish, then save another copy of the attachment to the file system on their own machine.

What’s new?
In essence all of this was possible in earlier versions of Access, using OLEDB to link or embed an object - but there were problems with OLEDB. For a start the embedded objects could be much larger than the original file in the file system. The reason for this is explained in an excellent page on Microsoft’s website about attachments.

As that says: “By default, OLE created a bitmap equivalent of the image or document. Those bitmap files could become quite large - as much as 10 times larger than the original file. When you viewed an image or a document from your database, OLE showed you the bitmap image, not the original file. By using attachments, you open documents and other non-image files in their parent programs, so from within Access, you can search and edit those files.”

In fact, with the new data type, the attachment is compressed wherever possible, so the situation is now considerably better. A maximum of 2GB of data can be attached, with each attachment limited to 256MB.

However, what about the fact that we can attach multiple files to one field in a single row? Doesn’t this break one of the fundamental rules that define a relational database? Apparently not because, as Microsoft explains on the website above, “attachments do not break any design rules, because as you attach files to a record, Office Access 2007 creates one or more system tables and uses those tables behind the scenes to normalize your data. You cannot view or work with those tables”. So that’s all right then.

Well, no it isn’t; much as I like the attachment data type, this statement is simply smoke and mirrors. If the underlying tables cannot be viewed or used, then to all intents and purposes they don’t exist. This attachment process effectively puts multiple items into one field and therefore does break the atomicity rule of relational databases, which is precisely the problem with multi-valued fields.