Hi Charlie,

I ended up going the route you suggested here and all works fine except for one situation. If the user only selects 1 field for the mailmerge, Word can't figure it out and prompts the user for what the field and record delimiters are. Any ideas how to correct this behaviour?

>Hi Colin,
>I decided that Comma Separated Value (CSV) files were superior to FOX2X type DBFs for doing MailMerge work. I believe they work better because they don't rely on ODBC (as much) to serve up the data into Word. See: http://www.officevba.com/features/2000/10/vba200010cm_f/vba200010cm_f.asp
>After reading this article I realized that even with text files, ODBC may be invoked. If the ODBC driver changes the user may still end up with the "Can't Open DataSource" dialog in Word (not a good thing). To always use Word's internal text converter, and be less affected by ODBC updates, the article suggests using a file extension that ODBC won't recognize. I chose CWH. To me it means Commas with Header, but it could have been ".ZZZ"; just so Word will try to use it as text. For example we might:
>SELECT Desired recordset FROM Something WHERE ItsWhatWeWant
>COPY TO (PathAndFileName.CWH) TYPE CSV
>to get a CSV file with the desired records in a local temp file location.
>We can then go into Word and create a mail merge document using this CWH datasource. If you save the document, then rename the CWH file, the next time you open the document the Can't Find Data dialog will come up since the original datasource is no longer there. One of your options is to Remove the Data and Header Source, which you should do. This means that the document is now unlinked from the original data source, and won't complain when it's opened. Alternatively, you may unlink the DataSource programmatically like this:
>Word = CREATEOBJECT('Word.Application')
>Word.Documents.Open(MyMergeDocName) && Open Document
>Word.ActiveDocument.MailMerge.MainDocumentType = -1 &&NotAMergeDocument
>Word.Quit(-1) && and Save without prompt.
>However you do it, manually or programmatically, you now have a document that is unlinked from it's datasource, but you can relink it at runtime to any file you want. (Assuming the same fieldlist.) Previously, I had to hard code the path of the CWH file in the MailMerge Doc, because I didn't know how to change it at runtime. Now I can just:
>Word = CREATEOBJECT('Word.Application')
>Word.Documents.Open(MyMergeDocName) && Open Document
>Word.ActiveDocument.MailMerge.OpenDataSource(PathAndFileName.CWH)
>and the datasource is relinked.Since the file extension is odd, ODBC isn't invoked. (which is good).
>By using the MailMerge.OpenDataSource method, I can programmatically set the datasource and locate it on each users temp (SYS(2023)), so multiple users can use the mailmerge simultaneously. I've set the MailMerge document as ReadOnly. SYS(2023) can be set via Config.FPW, so it may be different for you than me--that was a problem when I hard-coded the path.