As soon as you specify a DSN Word will use ODBC and you will lose Unicode
data. Even if Word happens to use the OLE Db provider for ODBC data sources,
I think you will lose the Unicode data (if that is what the problem is). I
am slightly cautious because you say it works when you connect manually
(i.e. I think you would have to use a .udl or .odc, not a DSN, for that to
work, but perhaps I am wrong).
The only way to use OLE DB (which should retain Unicode data) is to specify
a .udl or .odc file in the Name parameter. You can either use a valid .udl
or .odc that specifies the necessary connection information, or you can use
an empty .odc or .udl (e.g. create a Notepad file and put no text in it) and
specify all the connection information in the Connection parameter. But
AFAIK there is no way to avoid having that .udl or .odc. If you are
concerned about having to distribute that file, you can consider putting it
on a network drive that ican't locate the .udl or .odc at a URL such as
http:// or ftp://
Anyway, if you go the "blank .udl/.odc" route, what you probably need is a
connection string like:
Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=sa;Password=cobovubu;Initial Catalog=csasearch;Data Source=put the name
of the server machine here
You may need Persist Security Info=True
That uses the old SQL Server OLE DB provider. For the new one ("SQL Native
Client"), try using
Provider=SQLNCLI.1
(as far as I know most of the other parameters are the same). however,
whenever I have tried to use Word with the new "Native Client" and with a
logon and password rather than Windows Integrated Security, I have failed. I
don't think this is just down to the additional security configuration stuff
in newer versions of SQL Server, but maybe you will not have to face that
problem.)
Finally,
dsSubType = Word.WdMergeSubType.wdMergeSubTypeOLEDBText
is not the right subtype - you will probably be better off using
System.Type.Missing
I think this OLEDBText subtype is intended to specify that you want to open
a delimited text file using OLE DB rather than an internal converter or
ODBC, but I've never been able to verify that (The "Subtype" parameter isn't
exactly well-documented :-( )
--
Peter Jamieson
http://tips.pjmsn.me.uk
"Tony" wrote in message
...
Hi,
I am using Word 2003.
Here is the connection string I use:
"DSN=csasearch;DATABASE=csasearch;uid=sa;pwd=cobov ubu;", str,
"C:\Contract.doc"
the sql statement is
select * from vw1
----------------------
the code is:
dsName = Nothing
dsFormat = System.Type.Missing
dsConfirmConversion = System.Type.Missing
dsReadOnly = System.Type.Missing
dsLinkToSource = System.Type.Missing
dsAddToRecentFiles = System.Type.Missing
dsPasswordDocument = System.Type.Missing
dsPasswordTemplate = System.Type.Missing
dsRevert = False
dsWritePasswordDocument = System.Type.Missing
dsWritePasswordTemplate = System.Type.Missing
dsConnection = connection
dsSQLStatement = sql
dsSQLStatement1 = System.Type.Missing
dsOpenExclusive = System.Type.Missing
dsSubType = Word.WdMergeSubType.wdMergeSubTypeOLEDBText
wordDoc.MailMerge.OpenDataSource("", dsFormat, dsConfirmConversion,
dsReadOnly, dsLinkToSource, dsAddToRecentFiles, dsPasswordDocument,
dsPasswordTemplate, dsRevert, dsWritePasswordDocument, dsPasswordTemplate,
dsConnection, dsSQLStatement, dsSQLStatement1, dsOpenExclusive, dsSubType)
Thanks for your response