Motivation for a Database

It seems like a good idea from the start. Take data that’s getting out of control in a massive Word document (or documents) and put it in a database like Microsoft Access. The advantages being the added functionality of customized grouping, sorting, filtering, and searching. If all that you need is a graphical user interface to manipulate and manage your data, then Access is enough. If all that you need to extract from your database are printed reports, then Access is enough.

The Problem with Access

What if you want to use the database to periodically generate those documents that were the motivation for creating the database in the first place? Access cannot generate reports which you can save to a file and later edit or attach to an email. Actually this is a half truth. If we think of documents as a combination of content and style/layout, then Access can rudimentarily export the content of a report to Microsoft Word, but it cannot export the style or the layout.

Using Mail Merge?

What about using some sort of Mail Merge with Word? In other words, set up a reporting “template” in Word with all the style and layout, and then pull in the data from Access. Turns out that this only works well when you’re pulling data from a single table (like a mailing list). If you want information to be pulled from several tables and you want the information to be grouped in outline fashion, using Mail Merge will not work.

• Activity X
  • Task 1
  • Task 2
  • Task 3
• Activity Y
  • Task 1
  • Task 2
  • …
• …

Using Adobe Acrobat and the PDF File Format

It seems then that taking the data out of Microsoft Word and putting it into Microsoft Access effectively isolates the information from ever being used again (in a format other than on paper). However, it turns out that with Adobe Acrobat, Access can generate PDF files which match exactly the reports which Access produces for on-screen and printed consumption.

The implication here is that large documents could be assembled with the combination of Access and Acrobat, and without using Word except to creating a table of contents (and other “pages to insert” not generated by the database). In fact even those ‘custom’ pages created in Word can be ‘exported’ as PDFs, and inserted into the final document. Since the Acrobat reader is free (and widely installed), PDF files can be attached to emails, made accessible via the web, and of course printed out.

PDF is not the Perfect Solution

To generate PDFs with Access, you must have the full version of Acrobat installed on your computer (academic license costs $60,
otherwise costs $200!).

PDFs can only be edited minimally (to make small corrections or edit typos)—the PDF file format is not intended for making extensive changes. Large changes are intended to be made using the application from with the PDF originated (be it Access, Word, Pagemaker, etc) and not in the PDF file.

When the primary reporting purpose of a database is the generation of large reports and documents, page numbering and table of contents creation becomes an issue. Neither Acrobat nor Access provides a suitable solution for automating this task. Very likely a custom table of contents will have to be created in Word (and exported to PDF) once the bulk of the document has been finalized.

Sources:

Hardwick, Keri. Lines/Graphics not retained in Word/RTF Export. http://www.mvps.org/access/reports/rpt0019.htm