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. 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 world’s 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 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 means you can develop a report exclusively in Microsoft Access and then print the report straight to a PDF format rather than first going to a Microsoft Word document then converting it into an Adobe Acrobat PDF file. The other key advantage is that if you are using an OLE object data type 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 it’s not been an easy task (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 organizations 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 dropdown menu into the Microsoft Access main menu bar and from this dropdown 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 directly to a third party. I think this feature alone is worth the AU$700 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 macros 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 right 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 organizations that need to write monthly reports, is “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 (one can hope), Microsoft has said that it will be including the functionality for producing PDF files from within Microsoft Office 2007. We hope this functionality will be included for Microsoft Access 2007, which means, in the next version, macro developers may be able to export directly to PDF format and then email directly to clients. If Microsoft does offer this in its new version, this to me would be the major reason I would recommend all companies upgrade to this new version. I wait in hopes 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 several for learning Microsoft Access) and they are available from his Web site.

[Article Source: EzineArticles.com]

[tags]access,report,pdf,ole,ado,adobe acrobat writer version 7[/tags]