Results 1 to 3 of 3

Export Report to Single PDF Pages

  1. #1
    02kev02 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012

    Question Export Report to Single PDF Pages


    I'm very new to access and VBA.
    I'm hoping someone has a solution or can provide a little bit of guidance.

    I would like to be able to export my reports so that each record is exported to a seperate PDF file and titled by the unique identifier for example.
    In Access, I only have the option of exporting ranges of pages, or the whole report combined.
    However, this makes it difficult when there are over 500 records that need to be seperated.

    Is there some sort of script that can automize this process?

    Thanks to anyone who can help!!


  2. #2
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    The Great Land
    My approach would be a VBA procedure that opens a recordset of the unique identifier and loops through the recordset and output PDF of report filtered by the identifier from the recordset. I recently did something quite similar to output PDFs of about 70,000 reports from legacy database. An extract of code:
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    rs.Open "SELECT LabNum FROM Submit;", cn, adOpenStatic, adLockPessimistic
    While Not rs.EOF
          DoCmd.OpenReport "LabReport", acViewPreview, , "LabNum='" & rs!LABNUM & "'"
          DoCmd.OutputTo acOutputReport, "", acFormatPDF, "drive:\path\" & rs!LABNUM & ".pdf", True
          DoCmd.Close acReport, "LabReport", acSaveNo
          'kill Adobe Acrobat so I don't have thousands of open pdf documents
          Dim objWMIService As Object, objProcList As Object, objProcess As Object
          Set objWMIService = GetObject("winmgmts:")
          Set objProcList = objWMIService.ExecQuery("Select * from Win32_Process Where Name = 'acrobat.exe'")
          For Each objProcess In objProcList
               objProcess.Terminate (0)
    I couldn't actually output all 70,000 reports in one batch run. Had to do it in groups of about 5,000, otherwise Access would crash.

    EDIT: I have since discovered the 'kill' code is not need if the True parameter of OutputTo is changed to False. The 6 lines of code following the 'kill' comment are then not needed.
    Last edited by June7; 10-30-2012 at 03:13 PM.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!


  3. #3
    02kev02 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2012
    Thank you so much for your quick reply!!
    I will have to dive into VBA and try to implement this

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-08-2012, 12:03 PM
  2. Export single record from a form to PDF
    By GraemeG in forum Programming
    Replies: 1
    Last Post: 04-10-2011, 01:33 PM
  3. Exporting single query and single report.
    By rfhall50 in forum Programming
    Replies: 2
    Last Post: 02-18-2011, 11:08 AM
  4. Replies: 0
    Last Post: 05-13-2010, 05:18 AM
  5. Blank pages between report pages
    By jonsuns7 in forum Reports
    Replies: 2
    Last Post: 10-01-2009, 05:06 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Tech Forums: Microsoft Office Forums