Solved

How do I protect a Word mail merge document with an Access data source?

Posted on 2007-11-28
10
1,193 Views
Last Modified: 2013-11-28
Hello.  I am using Access 2003 and Word 2003.  I want to use an Access button on a form to open a Word mail merge document and have fields in the Word document updated with data on the form.  I do not want the user to be able to change "anything" in the Word document.  The mail merge document is created and linked to the Access database, but the protect document controls are greyed out.  This is my first time integrating Access and Word...if you list code, please tell me where to put it.  Thank you.
0
Comment
Question by:Lawrence Barnes
  • 5
  • 4
10 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 20367526
First of all, are we clear that mail merge is appropriate here?

Mail merge is primarily a way of producing a Word document with one section per database record. It works by having a Main document with mail merge fields to mark the positions where the data is to go.

The output is usually another document without any fields.
0
 
LVL 3

Expert Comment

by:incrediblejohn
ID: 20370727

That's a tough call because the .dot template generates a new .doc that needs to be accessible to the mail merge. Mail merge kinda is a black bag, not sure you could capture the moment the doc is filled and change it to read-only.

You might want to generate an Access report, or a pdf or snapshot instead. I don't have enough information on your business process.

0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 20372206
Once you are in to programmatic control of mail merge, what you need is possible. In the light of the documentation, training or trial and error, the black bag of Mail Merge becomes transparent, or at least translucent.

Here is some simple code that uses the new 'allow only reading' type of protection.
It requires a mail merge main document, set up in Word using the mail merge helper (wizard) accessed via the Tools menu.

It uses early binding, so you will need to set a reference to the Microsoft Word object library.

Sub MailMergeEarly()
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
   
    Set wdApp = New Word.Application
    wdApp.Visible = True
    Set wdDoc = wdApp.Documents.Open("C:\MyFolder\MyMainMerge.doc")
    wdDoc.MailMerge.Execute
    wdApp.ActiveDocument.Protect wdAllowOnlyReading, , "Password"
    wdApp.ActiveDocument.SaveAs "C:\MyFolder\MergeResult.doc"
    wdDoc.Close False
    Set wdDoc = Nothing
    wdApp.Quit
    Set wdApp = Nothing
End Sub

0
 
LVL 5

Author Comment

by:Lawrence Barnes
ID: 20373504
GrahamSkan and IncredibileJohn
I chose mail merge because I thought it was the only option.  Below are more details.

This database is being used to generate contracts...which have a lot of Word formatting and need to be pre-filled and non-editable.  At this point there are around 30 different contracts covering a base of 100,000+ clients.  I was planning on having the database open up and populate a particular contract by referencing the path/filename which is also stored in the database.  I did not see how to re-create all of that formatting in the Access form.  I've tried embedding the Word document into the Access 2003 form, but get a "The OLE server may not be registered" error.  (I'm trying to get the .dll registered now, but it is fighting me.  If there's an alternate path, let me know.

GrahamSkan.. I'm a noob to many aspects of Access/Word.  Where do I place this code and how is it called?  (ie. on the button that opens the document?)  And by references do you mean the Microsoft Word 11.0 library that I can find in the modules/references section?

Thank you so much.
Lawrence Barnes
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 20374109
Hi Lawrence,

Yes, The code could be in, or called by an Access button's onClick Event.

You can set references in the VBA editor, via the Tools menu, References item.

Embedding the document does not buy you much, in my opinion. It it just as easy, and more manageable to call the Word application - as my code does. If you wanted to, you could leave the application open so that the user could edit the document.

Incidentally while you can prevent users from changing the document, it is much more difficult to prevent users from copying the document and changing the copy. Even if copy and paste is prevented, OCR scanning cannot be.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 5

Author Comment

by:Lawrence Barnes
ID: 20375255
GrahamSkan,

Looking at your code there's a "password" in the code.  Where is this password set?  In the mail merge document that is linked to the database...the protect document controls, once activated, are greyed out?

Lawrence Barnes
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 20375516
That is where it is set. You would put your own word for a password in place of the "Password" text.

Generally when a document is protected already, the menu item changes to Unprotect. However protecting a mail merge main document could stop the merge from working, so it's not a good idea, anyway.

You can protect the result document after the mail merge run, which is what the code does.
0
 
LVL 5

Author Comment

by:Lawrence Barnes
ID: 20376895
GrahamSkan
We are so close.  The adjusted code is placed on the button calling the action.  The Microsoft Word reference has been added to the module and the database is being compiled.  However, I'm getting an error.
*  When I click on the button an instance of Microsoft Word opens and I can see the document.  However the mail merge bar is greyed out.
*  At the same time I get an error message: Run-time error '4605': This method or property is not available because the document is not a mail merge document.
*  I verified the mail merge document by closing the document and the database and re-opening the document.  After it prompts me for connecting to the data source, it does open as a mail merge document and is linked to the database.
*  When I pressed debug the line wdDoc.MailMerge.Execute is highlighted.
*  The button that calls this is named btnMailMergeEarly and this code was placed on the on click event.

So.. what little piece did I miss?  I've attached the modified code.

Lawrence Barnes
Private Sub btnMailMergeEarly_Click()

    Dim wdApp As Word.Application

    Dim wdDoc As Word.Document

   

    Set wdApp = New Word.Application

    wdApp.Visible = True

    Set wdDoc = wdApp.Documents.Open("P:\Access Database\Contracts\BananaCt.doc")

    wdDoc.MailMerge.Execute

    wdApp.ActiveDocument.Protect wdAllowOnlyReading, , "jsic2633"

    wdApp.ActiveDocument.SaveAs "C:\My Documents\Bananas.doc"

    wdDoc.Close False

    Set wdDoc = Nothing

    wdApp.Quit

    Set wdApp = Nothing

End Sub

Open in new window

0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 20377188
The registry patch in this Microsoft article might be necessary. It's lack seems to cause various misoperation symptoms in mail merge programs.

http://support.microsoft.com/kb/825765
0
 
LVL 5

Author Comment

by:Lawrence Barnes
ID: 20383722
GrahamSkan

The patch was applied and worked.  The mail merge document is opening and creating a protected document.  This question was answered :)

However, I am posting a follow up question, I'm an error on saving the new document to the path.  I'm sure I have something set incorrectly for the saved file on that acct exec's pc.  Feel free to respond to that question :)

Thank you,

Lawrence Barnes
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I'm writing to share my clumsy experience in using this elegant tool so you can avoid every stupid mistake I made. (I leave it to the authorities to decide if this deserves a place in the Knowledge archives.)  Now that I am on the other side of my l…
This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now