Word Mail merge problem with SQLSever via Access

  • Thread starter Humberto Santos
  • Start date
H

Humberto Santos

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)
 
P

Peter Jamieson

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.

--
Peter Jamieson

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

With WrdDoc.MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:="", _
Connection:="ODBC;DRIVER=SQL
Click to expand...
Server;SERVER=AVFSQL;DATABASE=avf;LANGUAGE=;Trusted_Connection=Yes;Regional=
", _
 
C

cerullian

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.)
 
Top