Word Mail merge problem with SQLSever via Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Humberto Santos - 01 Oct 2004 11:49 GMT

Hi,
I cant perform a connection to SQL Server with this code:

With WrdDoc.MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:="", _
Connection:="ODBC;DRIVER=SQL
Server;SERVER=AVFSQL;DATABASE=avf;LANGUAGE=;Trusted_Connection=Yes;Regional=", _
SQLStatement:="SELECT * FROM TBL01_Faxes"
End With

(Im using Access 2002 with Windows XP linked to SQL Server 2000 in a Windows
2000 Server)

Reply to this Message

Peter Jamieson - 01 Oct 2004 13:42 GMT

Word's COnnection parameter does not accept quite the same syntax as Access
uses to make links etc. I do not think you will be able to use a DSN-less
connection in Word, so you will need something more like the following

If you have a file dsn in a file called c:\fdsn\f.dsn

With WrdDoc.MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource _
Name:="c:\fdsn\f.dsn", _

Connection:="FILEDSN=c:\fdsn\f.dsn;SERVER=AVFSQL;DATABASE=avf;Trusted_Connec
tion=Yes;", _
SQLStatement:="SELECT * FROM TBL01_Faxes"
End With

You may need to add an extra parameter:

Subtype:=wdMergeSubtypeWord2000

If you have a user or system DSN called mdsn, try

With WrdDoc.MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource _
Name:="", _
Connection:="DSN=mdsn;SERVER=AVFSQL;DATABASE=avf;Trusted_Connection=Yes;",
_
SQLStatement:="SELECT * FROM TBL01_Faxes", _
Subtype:=wdMergeSubtypeWord2000
End With

You will need to work out what you need in the Connection string - the
strings in there should override the ones in the DSN.

Signature

Peter Jamieson

> Hi,
> I cant perform a connection to SQL Server with this code:
[quoted text clipped - 3 lines]
> .OpenDataSource Name:="", _
> Connection:="ODBC;DRIVER=SQL

Server;SERVER=AVFSQL;DATABASE=avf;LANGUAGE=;Trusted_Connection=Yes;Regional=
", _

> SQLStatement:="SELECT * FROM TBL01_Faxes"
> End With
>
> (Im using Access 2002 with Windows XP linked to SQL Server 2000 in a Windows
> 2000 Server)

Reply to this Message

cerullian - 28 Oct 2004 00:00 GMT

You are right that the connection parameter for SQL Server does not
work the same as it does for Access. In fact, I've found you don't
need it at all. Here is the code that I've been using:

With ActiveDocument.MailMerge
.MainDocumentType = wdFormLetters

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

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

Where strConnect is the path & name of an ODC file containing the
connection string. (With Word2002 I've also gotten it to work with a
OLEDB-based UDL file as well.)

>Word's COnnection parameter does not accept quite the same syntax as Access
>uses to make links etc. I do not think you will be able to use a DSN-less
[quoted text clipped - 30 lines]
>You will need to work out what you need in the Connection string - the
>strings in there should override the ones in the DSN.

Reply to this Message