Convert Access Report to PDF file


I would like to programmatically convert Access report to PDF file using PDF995. Anyone has the idea how to do it?

Thanks in advance.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You install pdf995 as a printer.
Then select it as your printer in Access and run your report.


ourselvesAuthor Commented:
But I'd like to convert the report in VB code.
Well even in code you would still have to programmatically select pdf995 as your printer and then open your report.

Do you mean VB or VBA?
If VBA which version of Access?


10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

I have some code that programatically changes the default printer to Acrobat PDF Writer and back again.  It is used in the following manner when exporting a report:

call ChangeToAcrobat
Call ChangePdfFileName(FileName)
DoCmd.OpenReport "ReportName"              'Print the report to acrobat
DoCmd.Close acReport, "ReportName"
ResetDefaultPrinter                                   'reset the default printer

Here are the Acrobat Functions.  I put them all in a seperate global code module:

Option Compare Database
Option Explicit

'This code is based on code from:
' Microsoft Access 95 How-To
' (c) 1998 Ken Getz and Paul Litwin
' All rights reserved.

Declare Function aht_apiGetProfileString Lib "kernel32" Alias "GetProfileStringA" (ByVal strAppName As String, ByVal strKeyName As String, ByVal strDefault As String, ByVal strReturned As String, ByVal lngSize As Long) As Long
Declare Function aht_apiWriteProfileString Lib "kernel32" Alias "WriteProfileStringA" (ByVal strAppName As String, ByVal strKeyName As String, ByVal strValue As String) As Integer
Declare Function WritePrivateProfileString Lib "kernel32" Alias "WritePrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpString As Any, ByVal lpFileName As String) As Long

Type aht_tagDeviceRec
   drDeviceName As String
   drDriverName As String
   drPort As String
End Type
Public drexisting As aht_tagDeviceRec

Public Function ChangeToAcrobat()
'This function gets the current default printer, saves it in the variable drexisting and then sets the default printer
'to be the acrobat writer to generate the pdf file
Dim dr As aht_tagDeviceRec

If ahtGetDefaultPrinter(drexisting) Then                'Get the current default printer and save it
    dr.drDeviceName = "Acrobat PDFWriter"                   'set the default printer to be acrobat writer
    dr.drDriverName = "PDFWRITR"
    dr.drPort = "LPT1:"
    Call ahtSetDefaultPrinter(dr)                      'reset the default printer
End If
End Function

Function ahtGetDefaultPrinter(dr As aht_tagDeviceRec) As Boolean
Dim strBuffer As String

strBuffer = ahtGetINIString("Windows", "Device")

If Len(strBuffer) > 0 Then
    dr.drDeviceName = ahtGetToken(strBuffer, ",", 1)
    dr.drDriverName = ahtGetToken(strBuffer, ",", 2)
    dr.drPort = ahtGetToken(strBuffer, ",", 3)
    ahtGetDefaultPrinter = True
    ahtGetDefaultPrinter = False
End If
End Function

Private Function ahtGetINIString(ByVal strGroup As String, ByVal strItem As String) As Variant
   ' Get a string value from the WIN.INI file. For example, the following, in the Immediate Window:
   ' ? ahtGetINIString("Windows", "Device") might display: HP LaserJet 4,HPPCL5E,LPT1:
Dim intChars As Integer
Dim strBuffer As String

strBuffer = String(255, 0)
intChars = aht_apiGetProfileString(strGroup, strItem, "", strBuffer, 255)
ahtGetINIString = left(strBuffer, intChars)
End Function

Function ahtGetToken(ByVal strValue As String, ByVal strDelimiter As String, ByVal intPiece As Integer) As Variant
Dim intPos As Integer
Dim intLastPos As Integer
Dim intNewPos As Integer

On Error GoTo ahtGetTokenExit

' Make sure the delimiter is just one character.
strDelimiter = left(strDelimiter, 1)

' If the delimiter doesn't occur at all, or if
' the user's asked for a negative item, just return the item
' they passed in.

If (InStr(strValue, strDelimiter) = 0) Or (intPiece <= 0) Then
    ahtGetToken = strValue
    intPos = 0
    intLastPos = 0
    Do While intPiece > 0
        intLastPos = intPos
        intNewPos = InStr(intPos + 1, strValue, strDelimiter)
        If intNewPos > 0 Then
            intPos = intNewPos
            intPiece = intPiece - 1
            ' Catch the last piece, where there's no trailing token.
            intPos = Len(strValue) + 1
            Exit Do
        End If
    If intPiece > 1 Then
        ahtGetToken = Null
        ahtGetToken = Mid$(strValue, intLastPos + 1, intPos - intLastPos - 1)
    End If
End If

   Exit Function

   MsgBox "Error in ahtGetToken: " & Error & " (" & Err & ")"
   Resume ahtGetTokenExit
End Function

Private Function ahtSetDefaultPrinter(dr As aht_tagDeviceRec) As Boolean
Dim strBuffer As String

' Build up the appropriate string.
strBuffer = dr.drDeviceName & ","
strBuffer = strBuffer & dr.drDriverName & ","
strBuffer = strBuffer & dr.drPort

' Now write that string out to WIN.INI.
ahtSetDefaultPrinter = (aht_apiWriteProfileString("Windows", "Device", strBuffer) <> 0)
End Function

Public Sub ChangePdfFileName(NewFileName As String)
Call WritePrivateProfileString("Acrobat PDFWriter", "PDFFileName", NewFileName, "c:\winnt\system32\spool\drivers\w32x86\2\__pdf.ini")
'Call aht_apiWriteProfileString("Acrobat PDFWriter", "PDFFileName", NewFileName)
End Sub

Public Sub ResetDefaultPrinter()
   Call ahtSetDefaultPrinter(drexisting)    'Reset the default printer
End Sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
'This code is based on code from:
' Microsoft Access 95 How-To
' (c) 1998 Ken Getz and Paul Litwin
' All rights reserved.

Then you shouldn't be publishing it.  This is not public domain code. You acquire rights to using it by buying the book.

That's untrue.  I received the code from a webpage with a disclaimer stating that the code could be used in any application as long as the copyright.
Here is the associated web page.
I apologise totally.

When code is placed in to the Access Web it usually carries embedded comments on it's permitted use.

No Problem.  The legality surrounding software use is a sticky subject.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.