Mailmerge Querystring Problems

[hals left]

I am trying to filter a mailmerge with VBA but cannot get word to
accept any WHERE clause on the mailmerge querystring property.

I dont think its anything to do with the content of the filednames or
the actual data because every where clause fails, even this:

Dim strSQL As String
strSQL = "SELECT * FROM LettersToCentres WHERE 1=1"
ActiveDocument.MailMerge.DataSource.QueryString = strSQL

I think it is anything after the view name as even this fails with the
same error.

Dim strSQL As String
strSQL = "SELECT * FROM LettersToCentres ORDER BY Office DESC"
ActiveDocument.MailMerge.DataSource.QueryString = strSQL

Even simple lookup tables with Integer only data fails.
strSQL = "SELECT ID FROM tblNumbers WHERE ID=4"

Everything I try gives the same error - 5638 Word could not parse your
query options into a valid SQL string.

I am using Word2002 SP-2 and connecting to views on SQL2000.

Without a where clause evrything works fine.

The connection is below, is there something in the connection I need to
change to make it work with WHERE clause?

Thanks.
hals_left


ActiveDocument.MailMerge.OpenDataSource Name:= _
"\\<path to odc file>\LettersToCentres - Prod Server.odc", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=SQLOLEDB.1;Password=<SQL Password>;Persist Security
Info=True;User ID=<SQL Username>;Initial Catalog=<Database Name>;Data
Source=<SQL IP Address>;Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;Workstation ID=<Machine Name>;Use
Encryption for Data=False;Tag w" _
, SQLStatement:="SELECT * FROM LettersToCentres", SQLStatement1:="", _
SubType:=wdMergeSubTypeOther

 

[Cindy M -WordMVP-]

Hi Hals,

If you go into the Word interface. Link to the data source. use Word's
Query options to set a filter...

1. Can you set a filter successfully?

2. If you can, what does this return, in the Direct window

?ActiveDocument.MailMerge.DataSourcd.QueryString

Does it differ from what you've tried to use?

I am trying to filter a mailmerge with VBA but cannot get word to
accept any WHERE clause on the mailmerge querystring property.

I dont think its anything to do with the content of the filednames or
the actual data because every where clause fails, even this:

Dim strSQL As String
strSQL = "SELECT * FROM LettersToCentres WHERE 1=1"
ActiveDocument.MailMerge.DataSource.QueryString = strSQL

I think it is anything after the view name as even this fails with the
same error.

Dim strSQL As String
strSQL = "SELECT * FROM LettersToCentres ORDER BY Office DESC"
ActiveDocument.MailMerge.DataSource.QueryString = strSQL

Even simple lookup tables with Integer only data fails.
strSQL = "SELECT ID FROM tblNumbers WHERE ID=4"

Everything I try gives the same error - 5638 Word could not parse your
query options into a valid SQL string.

I am using Word2002 SP-2 and connecting to views on SQL2000.

Without a where clause evrything works fine.

The connection is below, is there something in the connection I need to
change to make it work with WHERE clause?

Thanks.
hals_left


ActiveDocument.MailMerge.OpenDataSource Name:= _
"\\<path to odc file>\LettersToCentres - Prod Server.odc", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=SQLOLEDB.1;Password=<SQL Password>;Persist Security
Info=True;User ID=<SQL Username>;Initial Catalog=<Database Name>;Data
Source=<SQL IP Address>;Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;Workstation ID=<Machine Name>;Use
Encryption for Data=False;Tag w" _
, SQLStatement:="SELECT * FROM LettersToCentres", SQLStatement1:="", _
SubType:=wdMergeSubTypeOther

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

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

 

[hals left]

Thanks Cindy
I see whats happening now.
For some reason, Word requires double quotes around all table,view and
column names.