Filtering loses data source in Word 2002

[Vince Woodley]

If the Mail Merge Recipients dialogue box is used to apply
a filter to the records of a simple Excel spreadsheet when
the document is saved, closed, and later reopened it
claims that the data source cannot be found.
The symptoms are identical to that described in
http://support.microsoft.com/default.aspx?scid=kb;en-
us;275934
only the cause and the workaround don't apply.

To demonstrate the bug create c:\simple.xls data source
with:
name number
a 1
b 1
c 1
d 2
e 2
f 3
g 4

Create a mail merge document using it *without* a fliter.
Save, open & close all you want and it will find the data
source.

Now use the Mail Merge Recipients dialogue box to apply a
filter (e.g. number = 1). Save, close & reopen and you'll
get the error.

A comparison of the "recovered text" on the two files
shows the only difference is:

filter.doc
XSELECT * FROM `Sheet1$` WHERE `number` = 1
XSELECT * FROM `Sheet1$` WHERE `number` = 1

&

non-filter.doc
2SELECT * FROM `Sheet1$`
2SELECT * FROM `Sheet1$`

*BOTH* files still contain the correct full path
c:\simple.xls! My suspicion is that word is attempting to
run the filter before it opens the data source and so hits
the error.

Any thoughts - or better still work arounds?

Regards
Vince Woodley

 

[Peter Jamieson]

Hi Vince,

I have seen this kind of thing, particularly Access data sources, and at
least one version of the problem was reported so I'm hoping it won't be
there in Word 2003. Whether that will eventually result in a fix for Word
2002 I do not know.

Unfortunately, I could not replicate the specific example you gave and am
wondering whether it might have been fixed in SP-2 (which is what I am using
here) or whether I haven't replicated your example correctly.

FWIW although I don't work for Microsoft and don't have access to the source
code, when I looked at "my" version of this problem
a. there was one problem to do with field names with spaces in them and
another problem for field names without spaces
b. it looked as if Word was storing two conflicting versions of its filter
information - in 2002, Word tends to store a /lot/ of information to support
the facility where users can select/deselect specific records, and it also
stores the SQL query version of the filter.

FWIW you can see this stuff much more clearly if you save the mail merge
main document "As a web page", then open the web page as plain text,e.g. in
Notepad. Although I don't have my test results to hand, deleting one
particular row - I think it was just above the <w:FieldMapData> sections -
solved some of these problems. However, that's no use for a workaround -
only for debugging.

I suspect the best you could probably manage is use VBA to set up the base
query in an OpenDataSource call, then
a. store the current query and unlink the document from the data source
before the document is saved/closed
b. relink the document to the data source and apply the saved version of
the query when the document is re-opened.

Don't fancy attempting that myself either though :-(

 

[Cindy Meister -WordMVP-]

Hi Vince,

If the Mail Merge Recipients dialogue box is used to apply
a filter to the records of a simple Excel spreadsheet when
the document is saved, closed, and later reopened it
claims that the data source cannot be found.

Yes, this is a known difficulty with both Excel and Access
datasource when using an OLE DB connection method. It's been
fixed in Word 2003 (last time I looked; hopefully it wasn't
broken again on the way to production!).

Your only options are:
1) Don't use the Recipients dialog box to filter
2) use DDE or ODBC to connect to the data source

You'll find instructions for the latter in the Mail Merge FAQ
on my website.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jan
24 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail

 

[Vince Woodley]

Hi Cindy,

It's been fixed in Word 2003 (last time I looked;
hopefully it wasn't
broken again on the way to production!).

Indeed the Word 2003 Beta refresh doesn't exhibit
the same behaviour. But then neither does Word 2000!
Sounds to me like Microsoft needs this to add it to
their knowledgebase & produce a patch for Office XP.

Your only options are:
1) Don't use the Recipients dialog box to filter
2) use DDE or ODBC to connect to the data source

You'll find instructions for the latter in the
Mail Merge FAQ on my website.

Thanks - I'll check it out.

Regards
Vince Woodley
PS Peter: Word 2002 (10.4524.4219) SP-2 & fully up-to-date.

 

[Vince Woodley]

Thanks for the URL. There's some really useful stuff
there but the plot thickens...

http://homepage.swissonline.ch/cindymeister/MM2002/Interf02
..htm#RecipDlg has:
"* With an OLEDB connection to some data sources (most
notably Excel, Access and *.dbf files), the Word document
will not correctly save the link to the database file if
filters are set using this dialog box list. When the main
merge document is opened again at a later date, Word will
inform you that it can't find the data source. There is NO
problem if _Query Options_ are set instead of using the
filtering features in the Recipients dialog box."

&

http://homepage.swissonline.ch/cindymeister/MM2002/Interf02
..htm#QueryOpt has:
"* The old Query Options can be reached by selecting
the "Advanced" entry at the bottom of the list. Here, you
can also sort the records, and create more complex
filters."

So in the previous example set up a non-filter.doc (to not
have to link the data source yet again!)

In the "Mail Merge Recipients" click on the drop down list
next to the column title (e.g. number).
At the bottom of the list click on (Advanced...)
The "Filter and Sort" dialogue box will appear
Select Field: number Comparison: Equal to Compare to: 1 OK
Mail Merge Recipients: OK
File Save As new-filter.doc, Close, Reopen gives the same
error.
So re-link the data source :-( and...

(based on your
http://homepage.swissonline.ch/cindymeister/MM2002/PrevVe02
..htm)


Use Tools, Customize, Commands, select All Commands
Drag MailMergeQueryOptions (Displays the Query Options
dialog box directly) on to the Mail Merge toolbar.
Click the new "Query Options" button
The "Filter and Sort" dialogue box will appear
Select Field: number Comparison: Equal to Compare to: 1 OK
The previously accessed window will take the focus (WHAT
THE HECK!!!)
Go back to Word (& check the query has taken but it should
have!) :-(
File Save, Close, and now...
Reopen does *NOT* give the same error
Although it will have thrown away the query! :-(

Almost there.
Regards
Vince Woodley

 

[Vince Woodley]

Hi Cindy

And, since the problem isn't of the "crash and burn"
type, I'm not certain it would get included in a patch.

I can always dream! ;-)

Anyway, after trying the "Query Option" route I've had a
chance to try out your second suggestion:

2) use DDE or ODBC to connect to the data source

and that works-around the problem. For those that want to
know...

On the Tools menu, click Options, and then click the
General tab. Select the Confirm conversion at Open check
box.
Next time Word opens the document with the data source
problem it will again lose the data source so Find Data
Source... yet again.
But then it will ask in a "Confirm Data Source" dialogue
how you want to connect to the data source. Select "Excel
Files via ODBC (*.xls) - OK.
In the example used a Select Table dialogue will appear
with no table available so click Options... and select all
of
the Table Options to Show: Tables; Views; System Tables;
Synonyms then OK. You'll now see Sheet1$ where the data is
so select that & click OK and the document will open. Now
you can use the Mail Merger Recipients dialogue to apply
filters and when you re-open the document later
it will remember both where the data source is AND what
the filter was. Phew!

Connecting with ODBC is on Cindy's site under "Mail merge
in Word 2002" then "Connecting as in previous versions"
but for those that want a direct URL it's
http://homepage.swissonline.ch/cindymeister/MM2002/ConnMe02
..htm#ConnPrev

PS Cindy, you might want to think about not using that
clever javascript & frame setup on your website as it
makes things very hard to point to (see the "Word 2002 Use
Word97 Mail Merge Manager" thread)?

Regards & many thanks.
Vince