Filtering loses data source in Word 2002

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Vince Woodley - 27 Aug 2003 15:32 GMT

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

Reply to this Message

Peter Jamieson - 27 Aug 2003 17:55 GMT

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 :-(

--
Peter Jamieson
MS Word MVP

> If the Mail Merge Recipients dialogue box is used to apply
> a filter to the records of a simple Excel spreadsheet when
[quoted text clipped - 46 lines]
> Regards
> Vince Woodley

Reply to this Message

Cindy Meister -WordMVP- - 27 Aug 2003 19:01 GMT

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 :-)

Reply to this Message

Vince Woodley - 28 Aug 2003 10:29 GMT

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.

Reply to this Message

Vince Woodley - 28 Aug 2003 11:23 GMT

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

Reply to this Message