Printing Access reports to Acrobat Distiller

[Sergey Poberezovskiy]

Hi,

Thanks to the post from Taylor Bryant

http://groups-beta.google.com/group/comp.database.ms-
access/browse_thread/thread/4aa8f190071e66fa/97378c41b2f482
a8#97378c41b2f482a8

I have managed to convert Access reports to .pdf using
Distiller - no cost involved.

The reason I am posting this message is that I have spent
quite some time surfing the Net and trying to find a
simple solution that does not rely on third party tools -
and finally found it.

I have made a slight change to the code as I have to be
able to choose whether to print the report or create a
PDF. So my print to pdf is as follows:

Private Sub printToPDFDistiller(ByVal vsReportName As
String, ByVal vsDestPath As String, _
ByVal destFileNameNoExt As String, ByVal vsFilter As
String)
Const sPDF As String = ".pdf"
Const sPrinterName As String = "Reports Printer"
Dim nErrNumber As Long
Dim sErrSource As String
Dim nOrientation As Long
Dim destFileName As String
Dim fileName As String
Dim portName As String
' stop screen repainting
Application.Echo False
DoCmd.OpenReport vsReportName, acViewPreview, , vsFilter
On Error Resume Next
With Reports(vsReportName)
' set the caption to the destination file Name
.Caption = destFileNameNoExt
nOrientation = .Printer.Orientation
' set the printer to our printer
Set .Printer = Printers(sPrinterName)
portName = .Printer.Port
' check the printer orientation
If .Printer.Orientation <> nOrientation Then
.Printer.Orientation = nOrientation
End If
End With
With Err
nErrNumber = .Number
sErrSource = .Source
End With
On Error GoTo 0
' if errored then cannot proceed
If nErrNumber Then
DoCmd.Close acReport, vsReportName, acSaveNo
Application.Echo True
Err.Raise nErrNumber, sErrSource, "'" & sPrinterName
& "' printer is not installed!"
End If
DoCmd.PrintOut
' do not forget to discard the changes
DoCmd.Close acReport, vsReportName, acSaveNo
' now copy the file to the correct location
destFileName = vsDestPath & "\" & destFileNameNoExt &
sPDF
fileName = Left(portName, Len(portName) - 5) &
destFileNameNoExt & sPDF
On Error Resume Next
Kill destFileName
FileCopy fileName, destFileName
' delete the original file
Kill fileName
' restore screen repainting
Application.Echo True
End Sub

Note that sPrinterName is the name of the driver you
install specifically for Access reports. And obviously
using FileSystemObject will make the code easier to read
and maintain - but that was not the point. The point is
that we do not have to change the default Printer, neither
for Access nor for an individual report - and it still
works!!!

Hope this was helpfull for someone out there...

 

[Guest]

Hi Sergey,

Can you tell me specifically which version of Distiller you use..
I have been,, in the past, able to do this with pre- 6.0 but not with 6.0.

Thanks,
GG

 

[Sergey Poberezovskiy]

5.0.5.2001101100

That is the one our client uses...

 

[Sergey Poberezovskiy]

GG,

Could you please clarify what exactly can you not do in
6.0? Can you NOT define a Printer with Distiller Driver?
Or Can you NOT print a report to this driver?

It would be good to know should my client decide to
upgrade their Acrobat...

Thanks in advance,

Sergey