?
Solved

MS Access - VBA ~ Create "locked" PDF from report

Posted on 2011-10-04
17
Medium Priority
?
1,186 Views
Last Modified: 2012-05-12
Good Morning.  Need a little help.  I'm working on a project that creates multiple PDF's from a n MS Access report for distribution to conference attendees.  It's basically a certificate that awards CEU's to medical staff for attending.

I've successfully create a routine to generate the PDF files for each attendee, which is working very well. However, after a brief sense of accomplishment, I realized that I wasn't quite finished.  I need to 'lock' the PDF's so that the attendees cannot change them.  Finding a solution has proved to be more difficult then I initially thought it would be.

I see two options.  1) Lock it during the creation phase.  2) Lock it as part of a post process that opens the PDF, assigns a password to lock it from editing, and then closes the document.

The code to 'create' the PDF's is pretty simple and works very well.  Basically, three lines of code.

DoCmd.OpenReport "rptCEUCertificate", acViewPreview, , MyFilter
DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & MyFilename, 0
DoCmd.Close acReport, "rptCEUCertificate"

Would appreciate thoughts on how to either assign the security at creation or to open each file and assign the permissions as a post process.

THANKS!

0
Comment
Question by:careybroph
  • 9
  • 4
  • 2
  • +1
17 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 36909975
What PDF printer device are you using?
0
 

Author Comment

by:careybroph
ID: 36910144
We use Adobe Acrobat 9.0 (I believe all users have the same version).  I suppose I could install a different option like PDFCreator if that makes it easier.  But would prefer to stay within what we are currently using if that's an option.  THANKS.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 36910349
You can automate the password assignment for your PDF document with a different PDF printer device, such as Bullzip or PDFcreator.
http://www.bullzip.com/products/pdf/info.php

It might be possible to do this with Acrobat, but I can't assert that based on personal experience.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36910364
Just to be clear, there is no way to "Lock" any file from being edited.
There will always be a way for a determined user/hacker to change the file...

By default a PDF is supposed to be "Read Only", but again, there are utilities that will allow you to edit the PDF.

In Extreme cases you can save the PDF as an image, edit the image and save it back as a PDF.

The question is why the users have access to the PDF to begin with...?

The printed PDF should be given to the employee, and the "File" itself should be stored on a protected folder on a server somewhere.

JeffCoachman
0
 

Author Comment

by:careybroph
ID: 36910452
While I understand that there is always a way for someone intent on circumventing the system to do so, it's a matter of 'due diligence".  It is very common for PDF files to have an 'editing' lock.  Particularly documents that are distributed on the web or via email in bulk.  For example, college transcripts or bank statements are most often locked to prevent editing.

The purpose of 'why' is not relevant to this discussion..  But as an fyi...  your bank or employer may provide on-line PDF's of statements or paystubs or W2's.  Most certification providers (e.g. MSDN and CIW) provide their certifications as PDF files which are distributed upon completion and scoring of exams.  But again, that's not particularly relevant to this topic.
0
 

Author Comment

by:careybroph
ID: 36910524
aikimark:  That's helpful.  But I'm still not sure how I would actually set the security.  The "DoCmd.OutputTo" feature doesn't seem to accommodate parameters for setting security.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36910711
<The "DoCmd.OutputTo" feature doesn't seem to accommodate parameters for setting security. >
Right.  It is using MS's built-in PDF creation ability.
The locking is something above and beyond that.
You have Acrobat 9 installed, so you do have the bits necessary to do the job, but Adobe documentation is...imperfect.
0
 

Author Comment

by:careybroph
ID: 36910942
Calling Adobe documentation 'imperfect" is being quite kind.  I've spent the morning on Google trying to find a way to 'add' the security after the fact.  Not much luck.  I figure there must be a way to do it because banks and other institutions generate locked PDF's all the time.  For example, I can't edit my bank statement. For my particular project, it's not 'critical'.  It's a limited scope project and the players are very ethical folks who's integrity is worth more than changing the number of CEU's the get for a conference.

I may have to contact Adobe Support, although I doubt the folks answering the phones will be able to help much with this type of problem.

I've up'd the points to 500 for someone who can point me in the right direction of how to code this beast.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36911026
carey,

Thanks for the clarifications...

<Calling Adobe documentation 'imperfect" is being quite kind. >
LOL.



0
 

Author Comment

by:careybroph
ID: 36911038
I believe I have found a solution.  There is a product called Axommsoft PDF Encryption that looks like it will work.  I'll run it as a post process after creating PDF's.  I can run it as a batch routine on the entire PDF folder and it quickly adds security to all of the PDF's.  Works quickly and smoothly.  Wish I could have done this as part of the PDF creation process, but not a bad "Plan B".

Thanks for those who attempted to help.
0
 

Author Comment

by:careybroph
ID: 36916303
I've requested that this question be closed as follows:

Accepted answer: 0 points for careybroph's comment http:/Q_27378973.html#36911038

for the following reason:

Found a product that does the trick as a post process. &nbsp;Perfect is the enemy of good. This will do just fine.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36911405
<Adobe documentation is...imperfect.>
Since profanity is crass, common and frowned upon, I aim for understatement.
I have no intention of being kind, only polite
Feel free in your own mind to substitute the Lewis Black spitting mad / Dennis Leary sarcastically profane phrases that should be used.
I know I certainly do when it comes to all things Adobe.
<grin>
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 2000 total points
ID: 36911612
Ok,
The first site describes how to set a reference to Acrobat in VBA and open a particular PDF
http://www.khk.net/wordpress/2009/03/04/adobe-acrobat-and-vba-an-introduction/
I haven't found one with the VBA syntax for securing stuff.
I have given up trying to find it, too.

This one is more interesting
http://www.justskins.com/forums/acrobat-7-0-pro-17509.html

You may be able to create another Adobe Acrobat printer instance and in Printing Preferences set that instance to secure everything it prints.
You'd need to change your VBA syntax to printing to the new specific printer, but it should be doable.

I don't have many machines left that have Acrobat installed, so it'll take me a while to test this.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36911633
<I haven't found one with the VBA syntax for securing stuff.
I have given up trying to find it, too.>
Although this looks VERY promising
http://www.advancedqtp.com/wp-content/uploads/ScriptingQTP/CH16%20-%20Accessing%20PDF.pdf
0
 

Author Comment

by:careybroph
ID: 36916304
Nick67 provided some very helpful info.  Cancelling close and assigning points.
0
 

Author Closing Comment

by:careybroph
ID: 36916309
Clearly a difficult question, with no 'obvious' answer.  Appreciated the extended effort made trying to find a solution.  The information was very helpful in providing direction.
0
 

Author Comment

by:careybroph
ID: 36955510
For those interested, I found a tool that can encrypt/lock PDF's.  The interactive version is VERY nice and allows you to encrypt/lock PDF's individually or in bulk (you can encrypt an entire folder of PDF's).  It provides various levels of control: no editing, no copying content, no access, no printing... all of the normal PDF security controls.  After purchasing, I realized that I couldn't call it from a command line (or from an external app).  I contacted them and they created a command-line version that works very well.  It is EXACTLY what I was looking for.  Strongly recommend it to those looking for such a tool...

http://www.pdfencryption.net/
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

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.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Sometimes we receive PDF files that are in the wrong orientation. They may be sideways or even upside down. This most commonly happens with scanned or faxed documents. It is possible to rotate the view of these PDFs with the free Adobe Reader produc…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Suggested Courses

850 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