[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Change Printer to PDF in Access with VBA

Posted on 2014-08-25
15
Medium Priority
?
1,250 Views
Last Modified: 2014-08-29
I have the following code, and I need to know how to incorporate code to have an Access Report output to a PDF file, and then after the output is completed, change Printer back to the original Printer.

I originally was using this same code with Windows XP and Office/Access 2003; since upgrading to Windows 7 and Office/Access 2010, it is taking 20+ minutes to output the report to PDF and email, where before the upgrades, it was taking just a few seconds to complete

Since upgrading my Windows and Office Professional, I installed Adobe Acrobat XI Pro.  I thought that maybe if I had VBA Code incorporated into my current code to actually change the printer to PDF, that it may speed things up, unless someone has some other insight as to how to resolve the latency issues I've encountered since upgrading my Windows and Office Professional.

Thanks,
gdunn59

Public Function SendEmailEmpCompletedRptNoErrorsNoAction(ByVal varEmpEmail, varAddlEmail As String)
On Error GoTo Err_handler
Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim objOutlookRecip As Object
Dim objOutlookAttach As Object
Dim AttachmentPath As String
Dim DisplayMsg As String
Dim strAttach1 As String
Dim strAttachTemp As String
Dim strOutputToTemp As String
Dim strAttachCopy As String
Dim strPTR As Printer

DoCmd.Echo False
DoCmd.SetWarnings False

' check to see if folder c:\users exists on user's hard drive, if not, create
    If Len(Dir("C:\Users", vbDirectory)) = 0 Then
        MkDir "C:\Users"
    End If
    
strOutputToTemp = "C:\Users\Audit_Completed" & "_" & InquiryNum & ".PDF"

'''Output to Temp Area (to C:\Users)
DoCmd.OutputTo acOutputReport, "rptAudit_Emails_EMP_NoErrorsNoAction", acFormatPDF, strOutputToTemp, False

'Set Attachments (from Temp Dir on C Drive)
strAttachTemp = strOutputToTemp

' Copy to Network
'strAttachCopy = "\\Wiw2pwpfle001\data\QA Database\Employee Audit Scorecard System\Audit_Completed\Audit_Completed" & "_" & InquiryNum & ".PDF" 'OLD PATH -- NOT WORKING as of 7-15-2014
strAttachCopy = "\\w2pwpfp001\data\QA Database\Employee Audit Scorecard System\Audit_Completed\Audit_Completed" & "_" & InquiryNum & ".PDF"

' Variable/Path (to copy from C: Drive to Network Drive Folder for Completed Audits
FileCopy strAttachTemp, strAttachCopy

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

'Create the email message and send
'Set objMessage = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
    .To = varEmpEmail & "; " & "seniorqaebrteam@wellpoint.com"
'    .CC = varAddlEmail
    ' Set the Subject, Body, and Importance of the message.
    .Subject = "Audit Completed With No Errors -- No Action Required as of " & Now() & ""
    .HTMLBody = "Attached you will find a copy of your Quality Audit Scorecard.   If you would like to challenge your audit, your response must be " & _
             "received within 2 business days of the receipt of this message. Challenges received after 2 business days will not be accepted." & _
             "<BR><BR>" & "All challenges must be completed using the proper challenge form found within the QA Audit Challenge Process (QLA02); challenges on the wrong form will not be accepted." & _
             "<BR><BR>" & "Please see your OE for assistance should you have questions on the challenge process.   Do not contact your auditor by phone to challenge an audit." & _
             "<BR><BR>" & "Remember that this audit is a way for us to help you achieve the goals and objectives set by management." & _
             "<BR><BR>" & "Sincerely," & "<BR><BR>" & "Your Medicare Programs Quality Audit Team"

    ' Add attachments to the message.
    If Dir(strAttachTemp) <> "" Then
       .Attachments.Add (strAttachTemp)
    End If

    ' Resolve each Recipient's name.
    For Each objOutlookRecip In .Recipients
        objOutlookRecip.Resolve
    Next

'        .Save
        .Send

End With

Set objOutlook = Nothing

DoCmd.SetWarnings True
DoCmd.Echo True

''Remove attachments from C:\Users
Kill strAttachTemp
''Kill strAttach1
''Kill strAttach2
''Kill strAttach3
''Kill strAttach4
''Kill strAttach5

Exit_Handler:
    Exit Function

Err_handler:
    DoCmd.CancelEvent
    MsgBox Err.Description
    Resume Exit_Handler
End Function

Open in new window

0
Comment
Question by:gdunn59
  • 7
  • 7
15 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 40284483
In A2010 ... you know you can directly specify PDF in the DoCmd.OutputTo command .... specifying acFormatPDF
One line of code does it ... since A2010 directly supports PDF ... no Adobe Acrobat required.

mx
0
 
LVL 1

Author Comment

by:gdunn59
ID: 40284523
mx,

Yes, that is how my code is written, but as I mentioned since upgrading to Windows 7/Access 2010, it is taking extremely long to process the report to PDF, whereas before the upgrades, it was only taking a few seconds (using the same code that I posted).

Any other suggestions?

Thanks,
gdunn59
0
 
LVL 75
ID: 40284723
Sorry ,,, some how I completely missed that line of code. For some reason, I thought you were still switching printers to do PDF, sorry.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 1

Author Comment

by:gdunn59
ID: 40284882
mx:

No problem.  Do you have any other suggestions?

Thanks,
gdunn59
0
 
LVL 75
ID: 40284897
I really don't, sorry.
 Here is why I was confused:

"and I need to know how to incorporate code to have an Access Report output to a PDF file, and then after the output is completed, change Printer back to the original Printer."

IRONICALLY, my code is also in a QA database. What are the chances ???

And prior to switching to A2010, I had to installed virtual PDF printer (required full Acrobat) and configure it a certain way ... then, temporarily change printers to that printer, print, then restore the default printer.
Created a PDF in a Temp folder, then copied up to a specific on our share drive, then deleted the temp PDF.  That typically took 10-15 seconds, and was prone to hanging up !

Now ... with the OutputTo and PDF option, it's 2-3 seconds and never hangs up.
0
 
LVL 1

Author Comment

by:gdunn59
ID: 40284905
mx:

So what did you do . . . "Now ... with the OutputTo and PDF option, it's 2-3 seconds and never hangs up . . . "?

Where you said it worked and only took 2-3 seconds and never hangs up, and you're using 2010 also?

Thanks,

gdunn59
0
 
LVL 75
ID: 40284915
I'm just using

DoCmd.OutputTo command .... specifying acFormatPDF

That replaced a  LOAD of code to change printers, etc.  

Are you saying that line of code is taking a long time to execute ?
0
 
LVL 1

Author Comment

by:gdunn59
ID: 40284966
Yes.  That line is where its taking 20+ minutes now.  Before my upgrades to Windows 7 and Office/Access 2010 it was only taking a couple of seconds.
0
 
LVL 1

Author Comment

by:gdunn59
ID: 40284975
In my variable for the output:  
strOutputToTemp = "C:\Users\Audit_Completed" & "_" & InquiryNum & ".PDF"

Should I remove the:
        & ".PDF"
at the end of the variable/string since I have it outputting to PDF, or does that matter?
0
 
LVL 75
ID: 40284994
Let me check my code at work tomorrow ...
Pretty sure I am including .PDF also ...

Very odd ...
0
 
LVL 1

Expert Comment

by:IT Project Mgr
ID: 40285466
Here is some generic code to switch the default printer - but as mentioned above, you should not need to do this.  Is the variable "InquiryNum" a public variable that is defined somewhere else?  I cannot see where it would know this value otherwise...

Dim prt As Printer

'current default printer
prt = Application.Printer

'set the default printer to the one you want - whatever your pdf printer is called.
Application.Printer = Application.Printers("Adobe PDF")  

'print something
DoCmd.PrintOut

'change it back to the original default printer
Application.Printer = prt
0
 
LVL 75
ID: 40286331
>>  Application.Printers("Adobe PDF")    <<

The problem here will be that you will get unwanted prompts using the standard Adobe PDF Printer.
That's why I had to create a 2nd version of the PDF Printer, then set properties such that the prompts did not occur.

mx
0
 
LVL 1

Author Closing Comment

by:gdunn59
ID: 40287173
mx,

For some reason the code/reports/queries that I was originally using when I had Windows XP and Office/Access 2003 just does not want to process quickly with Windows 7 and Office/Access 2010; so I went through the reports that I was having latency issues with and changed the record source from a query to a table.  I took the query that it was using as the data source before the upgrades, and changed it to a "Make Table" Query, and then used the table created from this query, as the data source.  Now it only takes a couple of seconds to process, same as before I upgraded.  

Still baffled as to why it wasn't an issue with the older versions, but anyway, it's working fine now with the Docmd OutputTo . . . code.

I would like to still award you the points since you were the first to reach out to me.

Thanks much,

gdunn59
0
 
LVL 75
ID: 40287194
wow ... that ... is very strange, and I have no explanation.
But the good news is you got it working :-)

mx
0
 
LVL 1

Author Comment

by:gdunn59
ID: 40293147
mx,

Yes very strange, but very happy I found another alternative to the issue.

Thanks again for all your assistance/time.

Have a blessed weekend and holiday!!!!!

gdunn59
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

834 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