Opendatasource sqlstatement

[Roberto]

Hi, I tried to merge using a SQL statement longer than 255 ch. but the
connection fails. In Sqlstatement1 property I write the second part of the
string which contains only a simple ORDER BY statement. Only Sqlstatement1=""
is accepted. Any suggestion?
This is the code with simplified SQL statement (it doesn't work anyway):
'-----------------------------------------------
Sub trial()
Dim mydoc As Word.Application,
Dim mysql1 As String, mysql2 As String, myconnectionstring As String
Set mydoc = ActiveDocument.Application
myconnectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Password=" & Chr(34)
& Chr(34) & ";User ID=Admin;Data
Source=C:\TRIAL\mydata.mdb;Mode=Read;Extended Properties=" & Chr(34) &
Chr(34) & ";Jet OLEDBatabase Password=" & Chr(34) & Chr(34)

mysql1 = "SELECT MyTable.* FROM MyTable WHERE MyTable .SURNAME=""Smith"""
mysql2 = " ORDER BY SURNAME"
mydoc.Documents.Add , 0

mydoc.ActiveDocument.MailMerge.MainDocumentType = 3


mydoc.ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\TRIAL\mydata.mdb", ConfirmConversions:=False, ReadOnly:=False _
, LinkToSource:=True, AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", WritePasswordDocument:="",
WritePasswordTemplate:= _
"", Revert:=False, Format:=0, Connection:=myconnectionstring _
, SQLStatement:=mysql1, SQLStatement1:=mysql2


mydoc.ActiveDocument.MailMerge.EditMainDocument

End Sub

'--------------------------------------
Thanks,
Roberto

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?Um9iZXJ0bw==?=,

1. Version of Word involved?

2. Are you running this from within Word, or a different application? If you're
running it from within Word you don't need Dim mydoc as Word.Application. Word
knows it's the application

3. You say your sample code doesn't work. On which line does it fail, and what's
the exact wording of the error message? Where did you get the syntax you're
using for the SQL?

You might try this:

"SELECT * From MyTable WHERE [Surname]='Smith' ORDER BY [Surname]"

Hi, I tried to merge using a SQL statement longer than 255 ch. but the
connection fails. In Sqlstatement1 property I write the second part of the
string which contains only a simple ORDER BY statement. Only Sqlstatement1=""
is accepted. Any suggestion?
This is the code with simplified SQL statement (it doesn't work anyway):
'-----------------------------------------------
Sub trial()
Dim mydoc As Word.Application,
Dim mysql1 As String, mysql2 As String, myconnectionstring As String
Set mydoc = ActiveDocument.Application
myconnectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Password=" & Chr(34)
& Chr(34) & ";User ID=Admin;Data
Source=C:\TRIAL\mydata.mdb;Mode=Read;Extended Properties=" & Chr(34) &
Chr(34) & ";Jet OLEDBatabase Password=" & Chr(34) & Chr(34)

mysql1 = "SELECT MyTable.* FROM MyTable WHERE MyTable .SURNAME=""Smith"""
mysql2 = " ORDER BY SURNAME"
mydoc.Documents.Add , 0

mydoc.ActiveDocument.MailMerge.MainDocumentType = 3


mydoc.ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\TRIAL\mydata.mdb", ConfirmConversions:=False, ReadOnly:=False _
, LinkToSource:=True, AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", WritePasswordDocument:="",
WritePasswordTemplate:= _
"", Revert:=False, Format:=0, Connection:=myconnectionstring _
, SQLStatement:=mysql1, SQLStatement1:=mysql2


mydoc.ActiveDocument.MailMerge.EditMainDocument

End Sub

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