Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2007-11-28
10
Medium Priority
?
1,228 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 2000 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

604 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