Recipients List SQL Changes

[paul h]

I have an Access application that opens up one of 20 different Mail Merge letters. No issues on the Access side. When the Word document opens I get the usual SQL warning, but sometimes i get a second warning with a different (maybe temporary and without a path) filename. I am using RTF files for the merge data. See the examples below.

First (and normal) Warning

 
 Opening this document will run the following SQL command:
 
SELECT * FROM T:\FITS_2007\Leters\MailMerge\qry_Not_Enough_Info_R.rtf
 
Data from your database will be paced in the document.  Do you want to continue?

2nd Warning

 
Opening this document will run the following SQL command:
 
 
SELECT * FROM A065428D.rtf
 
 
Data from your database will be paced in the document. Do you want to continue?

From here the document seems to merge correctly with the right data, but I'm concerned that the user may inadvertantly save the Word document while it is pointing at this temp file which will not get updated going forward. So I am wondering why this is happening and how can I prevent it from happening in the future.

Any ideas?

[macropod]

I suspect there is an issue with your Access code - unless, perhaps, you have a DATABASE field in the RTF file.

[paul h]

This also happens when I open these files manually.

[macropod]

In that case, unless these files are actually Word templates (i.e. .dot/.dots/.dotm extension), the most likely reason is that they contain DATABASE fields or the documents are corrupt. Corrupt documents can often be 'repaired' by inserting a new, empty, paragraph at the very end, copying everything except that new paragraph to a new document based on the same template (headers & footers may need to be copied separately), closing the old document and saving the new one over it.

[paul h]

Hello,

I'm back taking a look at this again. I'm wondering what you mean by this.

the most likely reason is that they contain DATABASE fields

Of course all the data originally comes from an Access database, but there are no links between the Word document and the database, only between the document and the RTF file. Is there something I should look for in the document?

I noticed that even though the Word doc goes through this 2 step confirmation process, it remains linked to the original rtf file. Also, each time it tries to link to the 2nd file, the file has a different name. Bottom line is that it doesn't seem to do any damage and the merge operation still works. I'm just really curious as to why this is happening

No luck trying to create a new document. The problem seems to persist.

[macropod]

A DATABASE field is not the same as having a database connection. An ordinary document can have DATABASE fields and a mailmerge main document must have a database connection. A mailmerge main document can have both a database connection and one or more DATABASE fields.

Have you tried 'repairing' the document as indicated in my previous post?

[paul h]

Originally Posted by macropod

Have you tried 'repairing' the document as indicated in my previous post?

Yep. I also tried building a new one from scratch too. Started with a blank page and linked to an existing recipient list and loaded a bunch of mergefields into it, saved it, then reopened it and had to go through the merge confirmation twice.

How can I tell if it has database fields in it?

[macropod]

Originally Posted by paul h

How can I tell if it has database fields in it?

You're unlikely to have them in a document you've built from scratch. That said, you could press Alt-F9 to expose the field codes. DATABASE fields will all start with the word 'DATABASE'.

Are you sure your qry_Not_Enough_Info_R.rtf file isn't itself a mailmerge main document or a document containing a DATABASE field?

[paul h]

Did not see any DATABASE fields in the main document. The rtf files are created and replaced each time the main document is opened and contain only merge data and headers.

[paul h]

One more thing I am going to try. I found a very similar letter that does not act this way and I am going to use it to replace one of the existing letters that gives me the double confirmation message. If successful I'll replace the rest using this method. These letters ALL used to link direct to my database and there's no telling what's buried inside them. I'll let you know what happens.

[macropod]

It seems as though your application is opening the RTF, creating a temporary copy, then using that as the mailmerge data source. Since these RTF files (and their temporary offshoots) are being used as the mailmerge data source, I have to wonder why you're using that format rather than a plain CSV format. I suspect you'd have less trouble with the latter.

[paul h]

I think you described it exactly. I was having some trouble with other formats. Can't remember just what it was, but I tried rtf as an experiment and all the little bugs went away, or at least at first. I've never used them before and didn't notice this mailmerge issue. I hadn't tried to automate a mailmerge in a number of years. I'm not wed to any particular format, just whatever works. I'll take a look next week.
Thanks,Paul