Exporting PDF/A Documents from Microsoft Access

In the last couple of weeks, I received multiple inquiries about how to export a PDF/A compliant PDF from Microsoft Access. There is little knowledge available online on how to achieve this. Some AI powered bots will tell you outright, it is not possible at all. – If this were true, there wouldn’t be a point in writing this text. Fortunately, it isn’t.

What is PDF/A

PDF/A is an ISO standardized version of the PDF file format and is registered at ISO with the id ISO-19005. The A in PDF/A stands for archivable. Long-term archivability requires that a file can be visually displayed in its original state even after a long time without any requirements beyond a standard compliant PDF viewer.

At least in Germany, using this file format is mandatory to comply with legal requirements to storing regulated documents (e.g. invoices) as digital files instead of archiving paper. – I know of multiple cases where tax office auditors asked about PDF/A conformity during tax audits, but no case yet where they actually verified the technical compliance of the PDF documents.

To achieve PDF/A compliance, a PDF file must meet a couple of criteria. The most important are:

There are multiple sub-versions of the PDF/A standard, currently PDF/A-1 to PDF/A-4 with the corresponding ISO standard ID also having suffixes of -1 to -4 for different versions of the standard.

In my opinion, the most important is PDF/A-3 because this version of the standard allows to embed XML into the PDF document, which is required for the German/French ZUGFeRD/Factur-X standard that is an option to satisfy requirements becoming legally binding for electronic invoices in the EU in the near future.

To denote the compatibility level with the standard designation, just append the letters a or b to the standard identifier. E.g., PDF/A-1b or PDF/A-3a.

Disadvantages of PDF/A

An obvious, but very small disadvantage, of the PDF/A file format is increased file size. If the fonts used in the document are embedded in the document, they need some space. This is negligible, however. For typical office documents you would only need to embed a couple of dozen characters from a handful of fonts. This should increase the file size by less than 20KB.

Another factor for increased file size could be that a more efficient compressions algorithm cannot be used for elements in the file due to patent issue. – I’m not aware of any concrete scenarios in that regard, but I assume the effect on file size will also be negligible for typical office documents.

These two general disadvantages appear to be irrelevant for us, as Access appears to always embed the used subset of fonts and does not change compressions algorithms, regardless of enabled PDF/A compliance.

The requirement of having to embed images used in the document instead of storing them externally and linking them is no disadvantage at all in my opinion. The risk of these links breaking is so big and the effect so annoying that it conflicts with the primary purpose of PDF files.

Not being able to embed audio and video resources in the PDF may be a significant disadvantage if you need this type of content in your PDF. However, this is not applicable to typical document PDFs we would export from an Access application. So, I don’t consider this a relevant problem.

So, for us Access developers, there is no real disadvantage in using the PDF/A format.

TAB-Character Bug

While discussing a draft of this text with a couple of other Access experts, I was made aware of a bug in Access that only happens when exporting a PDF/A compliant file; regardless of which of the approaches described below for exporting the PDF/A you use.

If the report you are trying to export to PDF/A contains a TAB character anywhere in text, be it in a label control or in the data displayed in any data bound control, the export will fail with an error.

If you are exporting the PDF using VBA, you will just see an error “2001 - You canceled the previous operation.”

Both are very generic error messages, you will also see for a variety of other problems with the export of a report, particularly those listed in the error message. Unfortunately, there is no clue to the real cause of the error.

The only workaround I am aware of is to remove the TAB character from the report. For labels, just delete the TAB from the label text. For data bound controls, you can use the Replace function to dynamically remove the character either in the underlying query or in the Control Source of the relevant controls. – As a TAB cannot be visually represented in a report anyway, this has no effect on the appearance of the report.

How to Manually Export a PDF/A File from Access

You need at least Access 2007 to export a PDF/A compliant file, in fact any PDF file at all.

Support for PDF generation is not specific Microsoft Access functionality, but it is a shared component across all Microsoft Office applications. So, it is even more shocking that no official Microsoft Office documentation mentioning the term PDF/A exists at all.

Exporting PDF/A compliant PDF file from Access is simple, if you know how to do it. – In other Microsoft Office applications, the process is similar when you use the application’s PDF export capabilities.

In the Access ribbon, just click the button “External Data” -> “Export” -> “PDF or XPS”.

The important step during this common operation is to click the “Options” button and in the then displayed dialog enable the “PDF/A compliant” checkbox. This will make Access export a PDF/A compliant document. – It is not possible to select a specific version of the PDF/A standard for the export. The PDF/A version of the generated file depends on the version of Access/Office you are using.

PDF/A-3 is the most relevant version of standard. So, for current Access 365 versions it is only a small deficit that you cannot control the version of the standard.

For older versions it might be more of a problem. Technically, every PDF/A-1 is also PDF/A-3 compatible, as there are only more content types allowed in the A-3 version of the standard. Converting from A-1 to A-3 is only relevant if you want to embed additional content in a post-processing operation after the export from Access. If you do that, you can simply adjust the PDF/A version number in the metadata of the file.

Optionally you can additionally enable the checkbox “Document structure tags for accessibility”. This will add a hierarchical document structure for screen readers. However, this alone is not enough to satisfy the requirements of the “Accessible” conformity level.

When you complete the export, you can check the “Save export steps” checkbox to store the definition of this export in the current database. – This will become relevant in the next paragraph.

Verifying PDF/A Compliance in a PDF file

How to verify PDF/A compliance in the resulting PDF file depends on your PDF viewer. The minimalistic PDF viewers integrated in most web browsers are not helpful here. They do not support showing the relevant PDF properties.

The required metadata information for the PDF/A standard can already be verified with a simple text editor. Just open the PDF file in a text editor and search for the term “pdfaid:part”. If this term is not found, your document is not PDF/A compliant. If it is found, the section in the file should look like this:

The part tag denotes the PDF/A version this document claims to be compatible with and the conformance tag denotes the compliance level.

Be aware that this is just metadata written to the file by the application, which generated the PDF. This is required, but on its own is not sufficient for standard compliance. If you really want to verify the compatibility, you need to open the PDF file in a more advanced PDF viewer, to see the relevant information in a more structured format. PDF-XChange Editor greets us with this message right away:

This is an important requirement of the PDF/A format which is clearly satisfied. Verifying the other requirements is not as straight-forward as we would need to check that certain stuff is not present in the document, so I’ll skip checking those manually. Instead, I upload the created PDF to the PDF Creator online checker for PDF/A compliance. The response is terse but satisfying: “The file is a valid PDF/A-3B document“

For the records: I also compared the results of accessibility checkers for the generated PDF with and without the “Document structure tags for accessibility” option enabled but I could not see any differences.

Exporting a PDF/A Compliant file with VBA

Automatically running such an export after you did it manually once and saved the export steps on the last page of the export wizard is easy. Just invoke the RunSavedImportExport method of the DoCmd object.

DoCmd.RunSavedImportExport "TheNameOfTheExport"

TheNameOfTheExport is the name you entered in the “Save as” textbox when saving the export steps during a manual PDF/A export.

This is super easy, but there is a problem with this. The export file name is embedded in the export specification. So, using this approach to run an export will always use the very same file name and path. This will hardly satisfy your requirements for the integration of PDF export in your own application.

Customized PDF/A Export with VBA

Luckily, there is a solution to customize these types of exports, even though it is not obvious and not particularly convenient.

When these save exports were introduced with Access 2010, there also was a new object added to the Access object library, the ImportExportSpecification, which is accessible through the ImportExportSpecifications collection property of the CurrentProject.

This collection and its ImportExportSpecification objects allow to add new, and to delete, run, and modify existing export specifications. – But how to configure such an export? There are no obvious methods on the ImportExportSpecification object to do this.

The XML property is the key! – But you probably would not have guessed so reading its documentation:

Gets or sets the Extensible Markup Language (XML) string that defines an ImportExportSpecification object. Read/write String.

So, we need to look at it and figure out how this is supposed to work ourselves. Here is the XML of a ImportExportSpecification that I created and saved using the manual dialogs for the PDF export.

<?xml version="1.0" encoding="utf-8" ?> <ImportExportSpecification Path = "C:\Users\myusername\Documents\Project Tasks.pdf" xmlns="urn:www.microsoft.com/office/access/imexspec"> <ExportPDF AccessObject="Project Tasks" ObjectType="Report" AutoStart="true" Quality="screen" UseISO19005_1="true" /> </ImportExportSpecification>

So, to configure a Saved Export with VBA code, we need build the XML with the above attributes and then assign it to the XML property of an ImportExportSpecification object.

Here is a VBA procedure that wraps the complete export of a PDF/A document. It creates a new temporary export specification from scratch and deletes it immediately after the export. So, it will not confuse users by permanently adding a new specification to the list of saved exports.

Public Sub ExportPdfA(ByVal ReportName As String, ByVal FileName As String, ByVal ExportQuality As AcExportQuality, _ Optional PromptOverwriteExistingFile As Boolean = True, Optional AutoStart As Boolean = False) Dim exportName As String Randomize exportName = "_tmpPdfExport" & str(Rnd() * 100) Dim quality As String quality = IIf(ExportQuality = AcExportQuality.acExportQualityScreen, "screen", "print") Dim xml As String xml = "<?xml version=""1.0"" encoding=""utf-8"" ?>" & _ "<ImportExportSpecification Path = """ & FileName & """ xmlns=""urn:www.microsoft.com/office/access/imexspec"">" & _ "<ExportPDF AccessObject=""" & ReportName & """ ObjectType=""Report"" " & _ " AutoStart=""" & LCase(AutoStart) & """ Quality=""" & quality & """ UseISO19005_1=""true"" />" & _ "</ImportExportSpecification>" Dim spec As ImportExportSpecification Set spec = CurrentProject.ImportExportSpecifications.Add(exportName, xml) spec.Execute PromptOverwriteExistingFile spec.Delete End Sub

If you need to filter the report to export to export only certain records, you can design the query for the report to be filtered by TempVars, which are set appropriately before running the export. I described this in more detail in the “Performance Considerations” section of my earlier text on PDF exports from Access.

Undocumented – PDF/A Compliance with DoCmd.OutputTo … acFormatPDF

Access 2007 and 2010 cannot export a PDF/A compliant PDF using DoCmd.OutputTo … acFormatPDF. Period.

Starting with Access 2013 Microsoft has silently introduced PDF/A support into the DoCmd.OutputTo … acFormatPDF method. – Unfortunately, it again is completely undocumented and feels not well thought through.

If you manually export a PDF and check or uncheck the “PDF/A compliant” checkbox (see above) and confirm the dialog with OK, the state of the option will be stored in the Windows Registry in the value:

HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Common\FixedFormat\LastISO19005-1

(The version number, 16.0 in the above line, depends on your version of Microsoft Office, of course.)

This Registry setting applies to all Microsoft Office applications, not only Access!

If the value of LastISO19005-1 is 0 or the key/value does not exist, Office/Access will not export PDF/A. If the value is 1, Office/Access will export PDF/A. – This also applies to PDF files generated with DoCmd.OutputTo!

I wouldn't recommend this approach. As the Registry setting is global to all applications, the risk of interference by and with other applications is fairly high. If you still want to use this, you must:

This might work acceptably if you generate a single PDF, which completes in seconds. However, if you create a whole batch of PDFs, which runs for several minutes, then there is a real risk that the user prints a PDF in another application and thus interferes with your programmatic PDF batch generation.

Conclusion

Access supports exporting to PDF/A standard. This is a very relevant feature if you need to create documents for long term archiving. It’s great that Access is capable of this because at least in my jurisdiction every application generating invoice PDFs must use the PDF/A standard.

On the other hand, it is inexplicable to me why this feature was not officially documented when this was initially added with the Saved Exports feature. Equally inexplicable is why it never was mentioned in any release notes or public documentation that now even the DoCmd.OutputTo method generates PDF/A compliant PDF files, depending on an obscure Registry setting.

I will never share your email with anyone. You can unsubscribe any time.
This email list is hosted at Mailchimp in the United States. See our privacy policy for further details.

Benefits of the newsletter subscription

How to Generate a PDF/A Compliant PDF in Microsoft Access