Access 2010 Runtime Gotcha With Report to PDF

One of my small business client’s is still using Microsoft Access 2000 for two of its databases.  There never was any need, nor was there a desire, to upgrade the version of Access when the rest of the Office suite was upgraded.

Now, Windows 7 is being rolled out across their office and they want to continue to contain costs.  At my recommendation, they started using the Access 2010 Runtime.  This will permit their databases to continue to function without change – or without too much change.

One thing that was discovered early during testing was that the Calendar control (MSCAL.OCX) is no longer supported in Access 2010.  However, it is possible to continue to use the Calendar if you copy the file and register it.

More importantly and just recently discovered was the errant output of one of their reports.  The function uses Stephan Lebans’ Report to PDF code to take a report and convert it to PDF format.  While this has worked for a very long time, with only a minor hitch or two as the DLLs changed, it has proven to be a valuable utility.

Imagine the client’s surprise when a recently created document was produced in A4 landscape format (11.69 x 8.27) – resulting in a loss of data on the report!  Well, I was surprised and, at first, figured that some random printer setting was at fault.  That wasn’t it at all.

Another aspect of Access 2010 that Microsoft changed was the elimination of the snapshot format for reports.  Because this is the “driver” for Lebans’ code, the output defaulted to – I don’t know what, but it didn’t generate a valid report.

On the plus side, Access 2010 allows you to create a report and send it directly to PDF.  So, once it was clear that that some kind of coding change was necessary, it was relatively easy to implement.

I simply had to test for the current version of Access, because the shop is still in transition. Those folks who haven’t been upgraded continue to use the Lebans’ code, while those who are using the Runtime, get to take advantage of the new functionality that Access 2010 provides.

Here’s a sample of the code:

{code type=HTML}

Dim blRet As Boolean

‘If greater than Access 2003, use the built-in PDF functionality
If Int(Val(SysCmd(acSysCmdAccessVer))) > 11 Then
Dim acFormatPDF As Variant
acFormatPDF = “PDF Format (*.pdf)”

DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF, strPDFName
blRet = True
Else
‘ Call Stephen Lebans’ convert function
blRet = ConvertReportToPDF(strRptName, vbNullString, strPDFName, False, False)
End If

{/code}

Now, everyone in the office can continue to produce PDF reports without any errors.