But I don't know what should I do after I setup a connection and where
should I write those code?
You should be able to set up a connection manually, but since you cannot
select your System DSN in the Open Data Source dialog box, you have to click
the MS Query button in that dialog and follow the dialog boxes in there. It
can be tricky. Unfortunately, you can also find that MS Query will return
the data to Word, but that Word then tries to connect again and fails. if
that happens, it is probably either because
a. Word truncates the ODBC connection string that MS Query sends back to
it, making it an invalid string or
b. The login security information is not getting through.
To connect programmatically, this is roughly what you need in VBA
Sub ConnectToSQLServer()
' if necessary, disconnect from the existing source
' (you probably do not need this with SQL Server)
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
' if necessary, set up the merge type abnd/or destination that you want
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.Destination = wdSendToNewDocument
' change <your_dsn_name> to your SQL server DSN name and
' write the query you need
' ActiveDocument.MailMerge.OpenDataSource _
' Name:="", _
' Connection:="<your_dsn_name>;Trusted_Connection=Yes", _
' SQLStatement:="SELECT * FROM Northwind.dbo.Categories Categories", _
' SQLStatement1:=""
' e.g.
ActiveDocument.MailMerge.OpenDataSource _
Name:="", _
Connection:="DSN=myserver1;Trusted_Connection=Yes", _
SQLStatement:="SELECT * FROM Northwind.dbo.Categories Categories", _
SQLStatement1:=""
End Sub
You probably also need to make the registry change described in the
following article:
http://support.microsoft.com/kb/825765/en-us
If you are unfamiliar with VBA, you can find out what to do with this macro
at
http://word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm
You may also find the following useful:
http://word.mvps.org/FAQs/MacrosVBA/DocumentEvents.htm
However, the main problem is likely to be "getting your connection string
rand query ight".
Generally speaking your DSN will contain everything you need to get
connected, except security information. If you are using builtin Windows
security, "Trusted_Connection=Yes" should be enough (you may not even need
that. If you are using SQL Security, you cannot store the login/password in
your DSN and you have to put the following in your Connection string, using
your login and password of course:
UID=mylogin;PWD=mypassword;Trusted_Connection=No;
You may find you also need network infromation in the connection string. The
following is fairly typical for a TCP/IP connection:
Network=DBMSSOCN;Address=the_name_of_your_server_machine,1433;
Also, you can specify a database in the connection string using e.g.
database=Northwind;
Then you may only have to use
SQLStatement:="SELECT * FROM Categories"
and so on.
If you have a long SQL query, construct it using both SQLStatement and
SQLStatement1 so that when the two parts are put together, they form the
exact string you need, e.g.
SQLStatement:="SEL"
SQLStatement1:="ECT * FROM Categories"
not
SQLStatement:="SEL "
SQLStatement1:="ECT * FROM Categories"
If you are trying to connect to a stored procedure, you will need more.
See how you get on with that lot.
Peter Jamieson
