Solved

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

Posted on 2007-11-28
10
1,215 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

707 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