Using ODC file with Word to get SQL Data

Question

I am trying to get data from a SQL View to a Word MailMerge Document.  I am constantly getting all the data in the SQL View, but I only want a partial list.  I would like for the following SQL Select Statement to be run to merge the data:

"SELECT * FROM MailMerge Where Username = 'administrator'"

Instead it always seems to run:  "SELECT * FROM MailMerge" so I get all the records.

I have edited the following section of the ODC file to contain my SQL Select Statement and it works when I double click the ODC and opens in Excel.  I can not get it to filter the data within a Word Document though.

       <odc:CommandType>Text</odc:CommandType>

       <odc:CommandText>select * from MailMerge where username='administrator'</odc:CommandText>

Is there anything else I can do?  I've tried re-creating my Word Docs pointing to the new ODC and nothing seems to work.

Answer 1

My solution was to just create a simple view in the database containing a clause to limit number of records. Then I just connected the ODC to the view instead:

Sample:

create view V_adress as

select * from adress

where paidYear < 2013

Answer 2

My solution, albeit inelegant, was to create an Excel workbook from the ODC.  (Interestingly, once created, the workbook does not rely on the ODC, only on the DSN.)  After changing the properties of the connection to refresh on opening file, I can then close the file and use it as a data source for the merge.  An extra step or so but at least it gets the job done.

g

Answer 3

I would like to do something similar.  Rather than "select * from clients" I'd like to use "select * from clients c

join contacts t on c.cid = t.cid

where contact_date BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01')

AND DATE_FORMAT(NOW() ,'%Y-%m-01')" to get all clients contacted in the previous month.

Modifying the odc has no effect, as described in the above.  Are there any methods to implement a more complex SQL statement than the simple "select * from ..."?

g

PS [in an edit]: Remarkably, if I tell Explorer to open the odc file in Excel I get the expected recordset. 

g