Forgetting source on "non-simple" SQL

[Paul Lautman]

When I open a mail merge document, Word tells me something like "Opening
this document will run the following SQL command: SELECT * FROM 'Sheet3$'
....". It doesn't tell me the actual DataBase that it'l be using but hey -
can't have everything!

Anyway, in my merge I only want records that have something in the "Name"
field. So I go to the "Mail Merge Recipients" dialog and using the Filter
drop down arrow, select (Nonblanks). I can then perform the merge.


BUT!!! If I save the main document with (Nonblanks) selected, when I
subsequently open it, I see the new SQL statement as "SELECT * FROM
'Sheet3$' WHERE ('Name' IS NOT NULL And 'Name' <> '')" as expected BUT!!! it
then tells me that it cannot find the data source, and I have to go through
the file dialogs to link it to the source once again, and in the process it
looses the (Nonblans) selection once again.

If I save it with the "simple" "SELECT * FROM 'Sheet3$'" (i.e. no filters
applied to the Recipients) then it remembers the data source, if I have a
filter applied then it remembers the filter but forgets the source.

So, how can I get it to remember BOTH the filter AND the source (using Word
2002 (10.6754.6626) SP3)

Any ideas?

TIA
Paul Lautman

 

[Doug Robbins]

See the following Knowledge Base article:

"Opening This Will Run the Following SQL Command" Message When You Open a
Word Document - 825765 at:

http://support.microsoft.com?kbid=825765

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Paul Lautman]

Not quite my problem.

Yes I see that prompt and yes when I select yes it works fine. I'm not
bothered about the prompt.

My problem is when I have set a filter on the data source and so my prompt
is not exactly that one, but one with the filter details included. In that
case, when I click yes, the data source is NOT attached.

 

[Paul Lautman]

Thanks for trying. I tried it out but all it did was to stop the "Opening
this document..." prompt from coming up.

However, if I had saved the document with the data filter set, the document
is still losing its attachment to the data source.

Anyone got any other ideas?

 

[Paul Lautman]

A post to Woody's Lounge (www.wopr.com) elicited support from one of the
administrators (Hans).

His investigations showed that this is a bug in OLE DB. Choosing ODBC (after
selecting "Confirm Conversion at Open") seems to work OK.

Hope this helps anyone else who comes across this problem.