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

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.


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

What PDF printer device are you using?
careybrophAuthor Commented:
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.
You can automate the password assignment for your PDF document with a different PDF printer device, such as Bullzip or PDFcreator.

It might be possible to do this with Acrobat, but I can't assert that based on personal experience.
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.

Jeffrey CoachmanMIS LiasonCommented:
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.

careybrophAuthor Commented:
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.
careybrophAuthor Commented:
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.
<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.
careybrophAuthor Commented:
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.
Jeffrey CoachmanMIS LiasonCommented:

Thanks for the clarifications...

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

careybrophAuthor Commented:
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.
careybrophAuthor Commented:
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.
<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.
The first site describes how to set a reference to Acrobat in VBA and open a particular PDF
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

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.

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
<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
careybrophAuthor Commented:
Nick67 provided some very helpful info.  Cancelling close and assigning points.
careybrophAuthor Commented:
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.
careybrophAuthor Commented:
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...
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.