Mail Merge with SQL Server

[David Cooper]

Does anyone know how to perform a DSN-less mail merge to SQL Server 2k?

I'd rather this was DSN-less but even with a DSN I'm having the following
error...

Number = 5174
Description =
This file could not be found.
Try one or more of the following:
* Check the spelling of the name of the document.
* Try a different file name.
(DSN=hbis_sql)

I have managed to dump the query results into a text document, but I'd like
to lose this step...

Cheers

 

[Peter Jamieson]

AFAIK there is no way to avoid a DSN when connecting to an ODBC mail merge
data source, except you might be able to do it via a .dqy file and a DDE
connection to MS Query - nasty even if possible.

Which version of Word and what parameters are you using in your
OpenDataSource call?

If you are not using Integrated Security, you will typically need to provide
a user password in a Connection string as ODBC does not store SQL Server
passwords in Machine DSNs

For a successful OpenDataSource call to an ODBC data source you need either
Name:=""
Connection:="DSN=machineDSNname;other stuff as required;"
SQLStatement:=whatever

for Machine DSNs or

Name:="FullPathNameOfFileDSN"
Connection:="FILEDSN=FullPathNameOfFileDSN;other stuff as required;"
SQLStatement:=whatever

for File DSNs

and if you are using Word 2002 or later you will need

SubType:=wdMergeSubtypeWord2000, at least where Name:=""

I don't use this stuff much but in some versions of Word you have to code
your SQL quite defensively to make it work. I can't remember specific
examples but e.g. you may find that you /have/ to use a table alias if you
want to specify particular columns in your SELECT, even where it is
syntactically unnecessary. AFAICR this is mainly when using Word 2002/2003
and OLEDB connections.

 

[Robert]

I have gotten the following code to work using either a .UDL or .ODC
file:

strConnectString = "I:\Credential\reports\Credential.udl"
strConnectString = "I:\Credential\reports\Credential.odc"

With ActiveDocument.MailMerge
.MainDocumentType = wdFormLetters

.OpenDataSource Name:=strConnectString, _
SQLStatement:=strSQL

.Destination = wdSendToNewDocument
.Execute Pause:=False
End With

Make sure SQL Server permissions are set up correctly in the file.

Robert

 

[David Cooper]

Hi Robert,

Thanks for the below, unfortunately I don't know what a .udl or .odc file
is?!

Kind Regards

David

 

[David Cooper]

Thanks Robert, managed to set a .UDL file after messing around a bit,
however I'm not really keen on saving a password in a file that can be
opened by anyone using notepad. Have you managed to get round this?