Solved

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

Posted on 2007-11-28
10
1,206 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

840 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