Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Mailmerge Querystring Problems
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 |
#2
|
|||
|
|||
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 :-) |
#3
|
|||
|
|||
Thanks Cindy
I see whats happening now. For some reason, Word requires double quotes around all table,view and column names. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems with Mailmerge and odc | Mailmerge | |||
Mailmerge formatting problems | Mailmerge | |||
Problems (again) getting Outlook contacts in Word 2002 Mailmerge | Mailmerge | |||
Problems migrating Word 97 mailmerge document to Word 2003 | Mailmerge | |||
File Corruption problems | Page Layout |