SQL Database Merge

[Al]

Greetings,
I would like to merge data from a SQL database. I run the code below in the
word document. However, the form's fields, firstname, and lastname do not
update from <<FirstName>> to say Tom, once the "execute" line runs. Am I
missing something to update the form's fields.

Maybe can someone provide working sample code? Thanks in advance.


-- CODE
ActiveDocument.MailMerge.CreateDataSource _
Name:="Pro_Users", _
SQLStatement:="Select * from tblUsers", _
Connection:="Persist Security Info=False;User
ID=xxx;password=psxxx;server=myserver;database=Pro;", _
LinkToSource:=True

ActiveDocument.MailMerge.Execute
-- END CODE
--


Regards,

Al

 

[Peter Jamieson]

1. Switch to using an OpenDataSource call instead of CreateDataSource. At
the moment, what is probably happening is that CreateDataSource creates a
Word document called Pro_Users.doc with a standard set of data fields and no
data. It may not even look at the SQLStatement and Connection you have
provided.

2. You have to choose whether to get your data using ODBC or OLEDB,
depending on the version of Word (2002/2003 can use either, before that you
can only use ODBC), and what drivers/providers are available for your
database - if you are using SQL Server, both options are available.

3. For ODBC, you have to create and use a DSN in the ODBC Administrator. You
cannot use a DSN-less connection as far as I know. if you create a Machine
(user/System) DSN called mydsn, you can use

ActiveDocument.MailMerge.OpenDataSource, _
Name:="", _
Connection:="DSN=mydsn;", _
SQLStatement:="Select * from tblUsers"

In Word 2002/2003 you will need to add the parameter

Subtype:=wdMergeSubtypeWord2000

If you create a File dsn called c:\dsns\mydsn.dsn , you will need something
more like

ActiveDocument.MailMerge.OpenDataSource, _
Name:="c:\dsns\mydsn.dsn", _
Connection:="FILEDSN=c:\dsns\mydsn.dsn;", _
SQLStatement:="Select * from tblUsers"

You may also need
a. to add parameters to the Connection string - If you are using a trusted
Connection, you may need to set Trusted_Connection=Yes, otherwise specify
Trusted_Connection=No;UID=xxx;PWD=psxxx; You can also specify the server
(SERVER=), Database (DBQ= or DATABASE=, I forget which off the top of my
head and other stuff)
b. to do stuff like put backquotes round your table name - Select * from
`tblUsers`

4. For OLEDB, you need either to set up a .odc file with the correct
connection info (go through OpenDataSource and in Word 2002/2003 you should
be able to spot the mechanism for doing this) or to specify a completely
empty .odc file and put all the relevant OLEDB connection info. in the
Connection parameter - I would get these from an existing .odc file or .udl
file if you have one.

Peter Jamieson