I'm trying to write a code that opens a word document template (from my database) and enters criteria from a query (within my database) into the template. Here's my current code:
Code:
Dim objWord As Word.Document
Set objWord = GetObject("S:\(My Name)\Affidavit.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the Northwind database.
objWord.MailMerge.OpenDataSource _
Name:="S:\(My Name)\Database19.accdb", _
LinkToSource:=True, _
Connection:="QUERY ClaimantList", _
SQLStatement:="SELECT * FROM [ClaimantList]"
' Execute the mail merge.
objWord.MailMerge.Execute
I cannot get this code to properly mail-merge my claimant list. Every time I run the macro, it opens word and prompts me with this:
![](http://img857.imageshack.us/img857/3461/odbc.png)
I tested the code with other inquiries and tables and it worked perfectly. However, I think I have discovered why.
1.) The queries that worked with this code did not have anything in the "criteria field". The query that I'd like to use does have a criteria field. It is triggered by the value of a certain text box within a form.
2.) I believe that having criteria in a query means it cannot be read as an OLE (Object linking and embedding) data source.
When I attempt to manually mail merge from the word document and I select OLE Database files (I have confirm data source on), I get a list of every table AND every inquiry that does not contain anything in the criteria field.
![](http://img819.imageshack.us/img819/4500/olepz.png)
However, when I select "Show All" at the "Confirm Data Source" prompt and select "MS Access Databases via DEE (*.mdb; *.mde), I get a list that includes all queries, including the one that contains criteria.
![](http://img716.imageshack.us/img716/7231/showall.png)
This leads me to believe that I may be able to write a working code to mail merge the contents of my query IF I can find a way to express it as a DEE source rather than a OLE source. I'm looking for how I might change the code for this effect.