How To Export A Microsoft Access Report as a PDF

Microsoft Access is one of the most awesome prototyping tools anyone in software development could ever use, however there are also some really serious downsides as well. I know the software purists will be rolling over in their graves with my comments but after 20 plus years of commercial software development for some of the worlds largest companies I stand by my comments.

Microsoft Access makes it very easy and very cost effective to develop a database application and then roll it out onto the desktop. One of the downsides to this application is that it has always been incredibly difficult to export reports from Microsoft Access to another format. Most Microsoft Access developers that I know who build these systems use third party applications to export reports like Crystal Reports or Microsoft Word. The downside to using Microsoft Word is that you cannot export OLE Objects, using the merge process, you actually have to use VBA Code in Microsoft Word to call the ole object via ADO. Well that is one technique anyway. Now, with Adobe Acrobat Writer Version 7 we now have a new more effective method.

The exporting of OLE Objects has always been a major downside to Microsoft Access and Microsoft Word, but I was recently working with Adobe Acrobat writer Version 7 and I found that this version of the software now installs itself in all major Microsoft Office Applications. This is a fantastic step forward for Microsoft Access developers as this mean you can develop a report exclusively in Microsoft Access and then print the report straight to a PDF Format, rather than first going to Microsoft Word document then converting it into a Adobe Acrobat PDF File. The other key advantage is that if you are using a OLE Object Datatype in a Report, the Adobe PDF file will accept that data type and show a picture if that is what you are looking for. A lot of my clients have wanted to use Microsoft Access to develop reports that include photos and distribute them using Microsoft Word but up until now that has been able to be done easily. (Without using extensive VBA Code) Adobe Acrobat 7 allows us to create a pdf file from a report with OLE Objects and include them as photos etc within the PDF File. This means organisations that want to distribute staff files or product descriptions can create them using Microsoft Access. First of all they create the descriptions as a report and then convert them to PDF Format using Adobe Acrobat Writer.

Adobe has provided two ways to generate your PDF report. First off it installs an Adobe Drop Down Menu into the Microsoft Access main menu bar and from this drop down menu you can then choose to create the report as an ADOBE PDF. Note you must open the report first. You can even use this menu function to create the report and then email it direct to a third party. I think this feature alone is worth the $700AUD you pay for Adobe Acrobat Writer.

The second method is to open your report and then choose the File menu and then Print. The Print dialog box will open and then you choose Adobe Acrobat writer as the Printer and then choose the OK button which will then start Adobe Acrobat and generate your PDF.

With this new method to build a PDF file, you can even use Visual Basic for Access to automate the print process and then email the file using MAPI or CDO. If you are one of those people who like to use macro's over using VBA there is no way that I can find where you can achieve this. If you could find someone who could create a module for you that automates this process you could use a macro then by calling the Function in the module from the macro window. All this of course is dependent on finding the write person to write that function. I have not done this directly, because I have been using a form with VBA Code to automate the creation of the PDF and then email it via CDO.

One of the questions I get asked a lot, especially by people from GLobal Organisations who need to write monthly reports,"Would it be possible to automate this process and have the reports email themselves once a month". The answer to this is, ABSOLUTELY YES! The catch though is you would need to leave Microsoft Access open on a machine and then have a developer to write some code that acts like a timer and then once the computer clock ticks over the Form could simply create the associated reports and email them via MAPI. This seems complex all in all but for a competent VBA programmer, there should not be any problems doing this.

When Microsoft Office 2007 is released in 2007 (hopefully), Microsoft has said that they will be including the functionality for producing PDF files from within Microsoft Office 2007, hopefully this functionality will be included for Microsoft Access 2007, which means in the next version, MACRO developers maybe able to export direct to PDF Format and then email direct to clients. If Microsoft does offer this in their new version, this to me would be the major reason I would recommend all companies upgrade to this new version. I wait in hope that this will be the case.

Chris Le Roy is a veteran of the software development having worked in the field for over 20 years and has been developing systems using Visual Basic 6, VB.Net, Microsoft Access and SQL Server, ASP, VB Script and Java Script. He currently runs a specialist Computer Training Centre in Townsville Australia, where people from all over the world, train to learn advanced techniques in programming in these applications. Many of his clients specifically focus on Microsoft Access development training. Chris has written a number of training programs, including a Microsoft Access Course and he also has a range of new Microsoft Office Courses