?
Solved

Convert Access Report to PDF file

Posted on 2003-11-28
9
Medium Priority
?
2,123 Views
Last Modified: 2013-11-28
Hi,

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

Thanks in advance.
0
Comment
Question by:ourselves
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 9836903
You install pdf995 as a printer.
Then select it as your printer in Access and run your report.

Pete

0
 

Author Comment

by:ourselves
ID: 9836922
Thanks
But I'd like to convert the report in VB code.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 9837030
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?

Pete

0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 2

Accepted Solution

by:
rthomsen earned 150 total points
ID: 9839480
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
Else
    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
Else
    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
        Else
            ' Catch the last piece, where there's no trailing token.
            intPos = Len(strValue) + 1
            Exit Do
        End If
    Loop
    If intPiece > 1 Then
        ahtGetToken = Null
    Else
        ahtGetToken = Mid$(strValue, intLastPos + 1, intPos - intLastPos - 1)
    End If
End If

ahtGetTokenExit:
   Exit Function

ahtGetTokenErr:
   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
0
 
LVL 77

Expert Comment

by:peter57r
ID: 9841795
'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.

Pete
0
 
LVL 2

Expert Comment

by:rthomsen
ID: 9850607
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.
0
 
LVL 2

Expert Comment

by:rthomsen
ID: 9850625
Here is the associated web page.

http://www.mvps.org/access/reports/rpt0011.htm
0
 
LVL 77

Expert Comment

by:peter57r
ID: 9850823
I apologise totally.

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

Pete
0
 
LVL 2

Expert Comment

by:rthomsen
ID: 9850910
No Problem.  The legality surrounding software use is a sticky subject.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question