External Files in Databases
In the same way that you turn a brief into a database you can also make a database out of the collection of information and documents on a design or building. The main difference is that a number of fields have to contain visual representations which cannot be reduced to numerical values or succinct textual descriptions. These representations range from conventional images (e.g., a floor plan in a pixel-based image such as JPG or PNG) to proprietary files of a CAD or BIM program. As explained in section Making a database of the previous chapter, there are two preferred manners of linking files to a field. The first is by choosing Attachment as the data type of the field. This allows you to link the field directly to the file containing the visual representation. In fact, you can link the field to any number of external files. When you double-click on an attachment field in a datasheet view you open the Attachments window where you can see and open the attachments already linked to the field, as well as add, remove or save attachments (Figure 1).
Figure 1.
Attachment field in Access 2012
You can have any number of attachment fields in a table but the ability to link several attachments to one field suggests that you may need only one. This is reinforced by the appearance of the field in the datasheet view: Regardless of the name you have given to the field, its default name is a paperclip icon, while the value of the field is the paperclip icon again followed by the number of attachments in parentheses. However, it is advisable that you distinguish between attachments (e.g., on the basis of file type (Revit or AutoCAD files), the representation type (3D model, floor plan, section, detail, etc.), design stage (conceptual, final, technical), and so forth. To make a different name visible for each attachment field you have to enter this name in the Caption field property in the design view (Figure 2). You can use anything as a caption of a field but the most consistent option is the field name itself (Figure 3).
Figure 2.
The caption of an attachment field in Access 2010 (design view)
Figure 3.
Attachment fields named after their caption in the datasheet view in Access 2010
The second way to link a file to a field is through data type Hyperlink. This data type does not integrate the external file in the Access database but merely indicates the path to the file so that the computer can open it outside Access (Figure 4).
Figure 4.
Attachment and hyperlink fields in Access 2012
The two ways may seem quite similar beyond the one-to-many connection in attachment fields but there is an essential difference: with attachments, the attached file becomes part of the Access database. If you open it, you open the file that is part of the database, not the original one. If you try to exit the attachment field while the attached file is open in an external program, Access warns you that you may lose any changes made in the opened attachment. With hyperlinks you can simply refer to your stored files (or to files on the Internet). In terms of information storage, attachments create an archive of safe copies but they also increase redundancy and the size of the database. Hyperlinks keep the database compact but are dependent on external factors (e.g., file storage and network access) that cannot be controlled from the database.
A third way of linking to external files that is available in Access, through data type OLE object, is not recommended because when external files are embedded in the database it increases the size of the Access file more than with attachments. With OLE you can also link the object instead of embedding it (OLE stands for object linking and embedding). This keeps the database compact but unfortunately you have no indication of the path of the embedded object, so you cannot control your files as transparently and reliably as with hyperlinks.