A Guide to Auditing Attachment Fields in Access Databases

Attachments are popular in several accounting contexts. Many of the same file types that can be attached to emails can also be attached to the records in databases. For example, a user could store PDF files of professional certifications in attachment fields, thereby helping to maintain complete records of employee accreditations. Examples of database attachment files include:

• Microsoft Word documents such as contracts, job applications, work orders and tax returns
• Photographs such as pictures of clients, assets and professional events
• Spreadsheets such as budgets and forecast projections
• Audio files such as WAV files of phone conversations
• Video files such as recordings of interviews with clients and job applicants
• Specialized files such as PowerPoint files of presentations and PDF files of legal documents

Because of the popularity of attachment fields, an auditor may encounter client data that include attached files or may be asked how best to store such information in database records. Some examples include contracts attached to customer or supplier records, employment records, various types of inspection reports and patient records. Clients may not give much thought to attachment fields, including why they should name or store them systematically. However, if attachment files require further inspection, auditors need to know how to create attachment fields in databases, how to advise clients why some naming or storage approaches are better than others, and how to query attachment fields effectively. These queries include the ability to automate tests for the:

• Existence of attachment files
• Absence of attachment files
• Ability to find specific types of attachments using various query tools

Microsoft Access is one of several databases that support attachment fields. Other popular systems include Oracle, Informix and most alternate Microsoft products, such as Microsoft SQL Server. Many database systems also allow users to download smaller data sets into Access (sometimes using third-party tools), which is a convenient feature for auditors who are more familiar with Microsoft tools than tools requiring SQL logic.

This article describes how to create and view attachment fields and how to audit attachment fields in Access databases. It further explains how to perform some useful audit tasks if an auditor encounters databases that include attachment fields. This article discusses this functionality using the example of organizing hiring documents that are associated with a job candidate search. This example is used because of its broad applicability. It assumes that readers have a working knowledge of Access, but not necessarily attachment fields.

The examples illustrate the tasks using Access 2016, but most of the discussions and figures also apply to Access 2013.

Creating and Viewing Attachments

Creating and viewing attachments in databases like Access is relatively straightforward. The discussions that follow briefly review these tasks for those unfamiliar with them.

Creating Attachment Fields
If a business owner wants to store electronic information about job applicants—for example, Word or PDF documents—one way to do this is to store them as attachments to each applicant’s record in an applicant database table. Figure 1 illustrates the design for this table, which lists some exemplary data fields and their data types. To create an attachment field:

1. Add a field for attachments to the main record list of fields.
2. Name the field (it does not have to be called “ApplicantAttachments” as shown in figure 1).
   
3. Select the “attachment” data type for the new attachments field from the drop-down list, as shown in figure 1.
4. To attach specific files to the attachments field, switch to the datasheet view (shown in figure 2) and double-click on the attachment field of a particular applicant’s record. This launches the Attachments dialog box (see the open dialog box in figure 2).
   
   Note: Access replaces the field name (“ApplicantAttachments”) of an attachment data-type field with a paper clip symbol. To display the desired field name instead of the paper clip symbol, specify the desired field name (e.g., “Applicant Letters”) in the “Caption” setting of the Field Properties for this field.
   
   Figure 2 illustrates that the content of attachment data-type fields (for example, in the first record) also contains the paper clip symbol. A “(0)” in the applicant-record Attachments field indicates that there are no attachments.
   
5. To add attachments, click on the Add button in the Attachments dialog box. The Choose File dialog box (not shown) will be displayed.
6. In the Choose File dialog box, select the particular file to attach to the record and then click OK in the Attachments dialog box. The system returns to the data sheet view of records (figure 2). The paper clip (attachments) field for the applicant record contains (1), indicating that the attachments field now contains one attachment.

Attaching Multiple Files to a Record
Normally, each field of a database record can only contain one piece of information. The exception is the attachment data-type field of a record, which can contain multiple files.

To add another attachment to an attachment data-type field, repeat steps 5 and 6 in the previous section. Figure 3 illustrates the example applicant record with a picture file attachment and a résumé file attachment.

The ability to store multiple attachments in the same data field can be a disadvantage if the field contains many files. For example, if an Access table of car accidents contains picture files of accidents and written report files about them, then a single attachment data-type field may not be the best way to store them. In these instances, auditors can advise clients to create two attachment data-type fields per record—one to store pictures and a second field to store written reports. This approach can also improve the auditability of the database records.

Viewing Attachment Fields
An auditor can inspect the contents of attachment fields in two ways—in the datasheet view and in a database form.

Datasheet View
To view attachments in the datasheet view:

1. Go to the datasheet view (shown in figure 4), and double-click on the attachment field of an applicant’s record. This launches the Attachments dialog box (see the open dialog box in figure 4).
   
2. In the Attachments dialog box, either click the attachment name once to select it and then click the Open button on the right, or double-click the filename itself.
   
   An attachment opens in its parent software, which Access launches when a user selects the attachment for viewing. For example, if the attached file is an Excel spreadsheet, then Access launches the Excel parent software.
   
   Opening attachments allows auditors to view photographs, Word documents and spreadsheet attachments; listen to an audio attachment; play a video attachment; or use other parent software to view an attachment in the same way that email software opens email attachments.

In the dialog box of figure 4, be careful not to click the Remove button. Clicking the Remove button permanently deletes the selected file as an attachment, and the action cannot be undone. If an attachment is accidentally removed, it must be added as an attachment to the record again, using the instructions in the “Creating Attachment Fields” section, beginning with step 4.

Database Form View
A database form is a convenient way to view photograph attachments. To view attachments in a database form:

1. Create a database form for a database record (figure 5). When a form becomes active, Access displays the first photograph (or the filename of another file type, if photographs are not the only file type attached) of the attachment field in the record.
2. To quickly review additional files (photographs and other file types) in an attachment field, click the displayed photograph or filename in the attachment. A navigation bar displays above the field, as shown in figure 5.
   
   Click the navigation bar arrows to scroll through and view additional photographs and the filenames of other file types that are stored in the attachment field—a convenient way to view several attachment photographs quickly and speed up the audit process.
3. To open and view an attachment that is not a photograph (e.g., a Word document, PDF file, audio file or video file) and for which only a filename displays, double-click the filename in the attachment area of the form. Access will launch the appropriate parent software and open the file for viewing.

Attachment Field Rules
Figure 6 lists some important Access guidelines that auditors should know.

Creating Audit Queries of Attachment Fields

One of the advantages of using databases such as Access to store various types of data is the ability to create queries to find information quickly—a way of automating the audit process. This advantage also applies to attachment fields. For example, it could be used to identify all employees who do not have a current picture on file. If all of the picture files were organized in system file folders, the search might require a lengthy manual inspection, even though the storage medium is electronic. Alternatively, if the pictures were stored in the attachment fields of an Access database, a simple query can immediately identify all records that are missing photographs.

This section presents different ways that auditors can examine attachment fields in the context of a database table of job applicants. The methods discussed in this section are valuable in a variety of additional auditing contexts, such as examining lending documents, supplier contracts or other important business records that are stored as record attachments.

Exact-match Queries
Queries enable database users to identify the records of a database table that satisfy specific search criteria. One of the easiest ways to search for specific attachment files is by filename—for example, to verify the presence of a particular attachment document for a specific record. In Access parlance, this is an example of an exact-match query.

Access can search all record attachment files in a table of records and display only the record(s) containing the desired file(s). For example, to verify the presence of a photograph file named JoshPicture. jpg, create a query to search for this filename directly. Enter the full filename, including the file extension (.jpg) in the Criteria cell for the Attachments field (figure 7). The query finds the record containing the photograph file, even if the record contains several files in addition to the queried file.

Figure 7 shows quotation marks around the filename. Access adds these marks automatically to signify that this is an exact-match query. Thus, it is not necessary to type them separately.

Parameter Queries
To view numerous pictures with known filenames, it may be helpful to use a parameter query to search for and display records containing photograph files, or view them in a database form (see the “Database Form View” section).

To create a parameter query (such as the one shown in figure 8):

1. Create the criterion “[What is the file name?].” Be sure to include square brackets around the term. At run time, Access displays the Enter Parameter Value dialog box for the “What is the file name?” criterion.
2. Enter the full filename, with its file extension, in the Enter Parameter Value dialog box, and click “OK.” Access will display the record containing the photograph file.
3. To search for and display another photograph attachment, rerun the parameter query in figure 8 with the different filename.
   

The advantage of using parameter queries is that the auditor does not have to create new queries to find every desired file attachment. Instead, the auditor can rerun the same parameter query as many times as needed, thus speeding up the audit process.

Wildcard Searches
If the full name of a desired attachment file is unknown, a wildcard search can be used to create queries based on partial information. For example, suppose several audio filenames begin with the term “PhoneInterview,” such as PhoneInterviewFilzen. wav and PhoneInterviewSimkin.mp4, in a job-applicant table. To find all attachments that begin with PhoneInterview, add an asterisk (wildcard) after the known part of the filename (e.g., PhoneInterview*) in the Attachment field Criteria cell portion of the query. Access will find all records whose attachment files begin with “PhoneInterview,” including PhoneInterviewSimkin.mp4 and PhoneInterviewFilzen.wav.

Multiple wildcards can be used in the same query. For example, suppose an attachment field contains the files “2016PhoneInterviewSimkin.mp4” and “2017PhoneInterview.wav.” An Access query using the criteria *PhoneInterview* (with wildcards at both the beginning and end of the search criteria) finds the records for both of the files, plus all records in the table that contain this criteria—not just the phone interview files for a desired client. This problem can be overcome by including the client name as an additional search element in a separate criteria field of the query (see the “Too Many Record Hits?” section).

Knowing how to use wildcards enables auditors to find, for example, all picture files ending in .jpg (using *.jpg as the search criteria) or .gif (using *.gif as the search criteria). These are searches that look for file types instead of filenames. In each case, Access automatically embeds the LIKE operator with the wildcard criteria, as illustrated in figure 9 for the first example. When the query in figure 9 runs, the displayed result shows all records with attachment filenames that end in .jpg.

The OR operator can be used in wildcard queries to find filenames satisfying two (or more) alternate endings (or beginnings). For example, to find .jpg or .gif files, use the compound criteria shown in figure 10. This query identifies all files with filenames ending in “.jpg” or “.gif.” An alternate way to express an OR condition is to enter each file type query on separate lines under the Attachments field, which might be easier if there are more than two or three conditions.

The availability of wildcard searches provides motivation for naming files systematically to facilitate the ability to audit them later. An example is using the naming convention PhoneInterview Name for the name of all files that are recordings of phone interviews.

Too Many Record Hits?
The query examples in this article are generic and may result in thousands of returned records in large database tables. One way to narrow the search is to use additional fields in a record to specify additional search requirements. For example, to see a list of all photograph files for the client Thomas Trenton, simply include the first and last name in the search criteria. Additional field criteria (figure 11) narrow the list considerably, because all search criteria listed on the same criteria line in an Access query specify an AND condition. The search criteria in figure 11 instructs Access to display all records containing .jpg or .gif files only for Thomas Trenton.

Testing for the Absence or Presence of Attachments
An auditor may want to confirm that the Attachment field in each record is not empty. This task can be automated in Access. For example, to confirm that all job-application records include résumés, use “Is Null” in the query to identify missing résumé attachments. Be sure to include a space in this term. Figure 12 illustrates the appropriate query.

When the auditor runs this query, the results are a datasheet that displays all job applicant records that are missing résumés in the Résumé Attachment field. This approach does not work for attachment fields that store résumés and other files, e.g., photographs, in the same attachment field. The query in figure 12 only lists records that contain nothing in their attachment fields.

Finally, it is also a simple matter to search for nonblank data fields using the NOT operator. In effect, the expression “Not Is Null” creates a query that displays all records that contain something in the data field of interest. Figure 13 illustrates the query for job applicants with résumés. The results are a datasheet listing all records that have files in their Résumé attachment field. For a certification application that stores PDF files of test results, for example, using the “Not Is Null” expression in a query allows an auditor to identify those employees who have taken a required test.

Conclusion

The advantages of using attachment fields in databases are considerable, enabling users to store multiple photographs, Word documents, spreadsheets, PowerPoint presentations and similar files in electronic formats; consolidate information from several locations; and perhaps come closer to the ideal of a paperless office. Because of these advantages, auditors may find themselves in a situation where they need to retrieve or verify information stored in attachment fields. Some key items for auditors to keep in mind regarding the use of attachments in Access include:

• Modern database software enables users to attach, and auditors to query, attachment files conveniently—for example, to create queries by filename, by file format, by client name or by some combination of these criteria.
• Encouraging clients to name files consistently, using embedded generic terms such as interview or photo, helps users to organize attachments logically and helps auditors to identify them more easily, as needs require.
• Auditors can test for the presence or absence of attachment files and identify types of file attachments using wildcard file extensions. However, Access does not allow auditors to test the contents of attached files.
• The information that is typically stored in a database is sensitive and attachments typically contain information that increases this sensitivity. For this reason, auditors should encourage clients to protect database information with passwords and create backup copies of their databases.