Solved

Access 2003 & Word 2003 - Mail Merge from Access

Posted on 2011-09-11
8
361 Views
Last Modified: 2012-05-12
Can I open a Word 2003 Mail Merge document from Access 2003 in VBA code if I am using that same database as the source for the mail merge document?

Currently receiving "Word was unable to open the data source" most likely due to exclusivity issues.

Code Below:
Dim objWord As Word.Application
    Dim objDoc As Word.Document
    Dim strdbPath As String
    Dim strSavedName As String


'Word
    Set objWord = CreateObject("Word.Application")
    Set objDoc = objWord.Documents.Open(Application.CurrentProject.Path & "\TestLetter.doc")
    objWord.Visible = True

    With objDoc.MailMerge
      .MainDocumentType = wdFormLetters
      strdbPath = Application.CurrentProject.FullName
      .OpenDataSource Name:=strdbPath, LinkToSource:=True, OpenExclusive:=False, AddToRecentFiles:=False, SQLStatement:="SELECT * FROM qryMergeLetter"
      .Destination = wdSendToNewDocument
      .Execute
    End With    'objDoc.MailMerge
    
    With objWord
      .Activate
      .Documents.Parent.Visible = True
      .Application.WindowState = 1
      .ActiveWindow.WindowState = 1
    End With    'objWord

Open in new window

0
Comment
Question by:Adam D
  • 4
  • 3
8 Comments
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 36521098
This should work unless you have opened the database for exclusive access.  Is it opened exclusive or shared?

Best regards,
Graham Mandeno
0
 
LVL 1

Author Comment

by:Adam D
ID: 36521128
Good Morning.

I do not specifically open it as exclusive.  I choose the database and select Open.  There is an option to choose your open method but I do not choose the Exclusive method.

I have read issues where something causes Access to enter exclusive mode when the code is running. I do not see any reason why my code would be causing this issue though.

Thoughts?  Thanks.
0
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 36521156
Good evening from this side of the world :-)

The first thing to check is the default open mode - go to Tools > Options > Advanced, and make sure "Default open mode" is set to "Shared".

Best,
Graham
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 36521159
You don't have any security on the database do you?  Either user-level security or DB password?
0
 
LVL 21
ID: 36521219
I found in Word 2003 that there were queries that it did not like. Since you are merging with the query qryMergeLetter tis may be the issue.

As a text try creating a make table query that save the data from qryMergeLetter to a table.  Try merging with the table.

I would urge you not to merge with an Access database. Word actually opens another instance of Access that load another  copy of your database.  This can cause lots of issues.

What I find best is to export your merge data to a csv file with teh first row being the field names. Have Word merge with the csv file.
0
 
LVL 1

Author Comment

by:Adam D
ID: 36522248
Good Morning/Morning (much later morning now on this side) :)

1. Default open is Shared in Access 2000 format (if that matters)
2. No security
3. I do understand Word will open another instance unfortunately but I would like to keep everything contained.  I could try it that way though.

As an alternative, which really would be the ultimate goal I think, is to create the merge document from the query, save that document so it is not an active merge then re-open the final document with no merge connections.

Best way to do that?

Thanks.
0
 
LVL 20

Accepted Solution

by:
GrahamMandeno earned 500 total points
ID: 36526381
Good morning/evening :-)

I have attached a massively cut-down version of my MailMerge class module.  (I needed to cut it down because there were too many dependencies on other modules - email, PDF, etc)

The attached file contains three functions:
- one to ascertain whether a file exists
- one to export a recordset or SQL string to a tab-delimited text file
- and the main one, which takes a SQL string, exports it to a text file, and then uses that text file as the data source for a mail merge.

I hope it's all straightforward.  Please sing out if you need further clarification.

Best regards,
Graham
 MailMerge.txt
0
 
LVL 1

Author Comment

by:Adam D
ID: 36567724
Thanks Graham.

The code looks good and although I would have preferred to keep it all together I decided to extract the data needed into an excel file format and tell word to look there instead.

However; instead of using your code. I used an easier one line method (see below).  Of course I had to have my other previously mentioned code and point Word to this new file.  But it works and works well.

Thanks for your and everyone's help.


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryMergeLetter", strMMFilePath, True

Open in new window

0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Introduction This tutorial provides instructions on how to properly format your Word document using the inbuilt tools provided. The benefits of using these tools means your documents are more accessible and easily portable to other applications an…
I would like to show you some basics you can do with Mailings in MS Word. It´s quite handy feature you can use for creating envelopes, labels, personalized letters etc. First question could be what is this feature good for? Mailing can really he…
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…
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

813 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

11 Experts available now in Live!

Get 1:1 Help Now