It's probably because some of the fields are Unicode and some are not,
and you won't see the Unicode ones via ODBC.
MS Word 2008
I assume you mean Word 2007 - especially with the .docx reference in
there - but as long as you mean any Windows version of Word from 2002
onwards, you may be able to fix this by changing the connection type to
OLE DB. However,
a. that will require that you create and distribute either a .udl file
containing the connection information, or a .odc file. Of the two, .odc
is probably the better choice as .udl seems not to work so well these
days, if at all.
b. In recent versions of Word, I have only ever been able to get this
to work when you are using built-in (Windows) authentication where you
do not have to provide a username and password in the connection string.
I've never managed to do it using "SQL Server" authentication.
You can either create a .odc by going through the process of connecting
to an existing data source, then using the New Source button (Word 2007)
- it should be reasonably obvious after that as long as you are using
the "old" SQL Server OLE DB Provider (SQLOLEDB), or you can (usually)
create an empty one (e.g. create an empty Notepad file and save it as
empty.odc) and put the connection information in the Connection
parameter of the OpenDataSource method. That has the advantage that you
only ever need to distribute one .odc for all your SQL Server merges.
For example, if your SQL Server machine is called "myserver", your
database is called "Permits, and you have an empty .odc called
c:\myodcs\empty.odc
then the following should work if you are using the /old/ SQL Server OLE
DB provider
Dim strConnect as String
strConnect= _
"Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=True;Initial Catalog=Permits;" & _
"Data Source=myserver;"
wrdDoc.MailMerge.OpenDataSource _
Name:="c:\myodcs\empty.odc", _
Connection:=strConnect, _
SQLStatement:=strSQL1, _
SQLStatement1:=strSQL2)
You may not need "Persist Security Info=True"
You /may/ also need to change the SQL statement either to specify the
table for each field (mytable.myfield etc.) or to use a table alias that
you then use for each field. (In theory you should not have to, but in
some cases Word seems to insist on it).
You have to pare the connection string down because Word only lets you
specify a string up to 255 characters, and connection strings are
typically much longer.
If you are using the /new/ SQL Server provider ("SQL Native Client"), to
use an empty .odc you would probably only have to change the Provider
name, e.g.
strConnect= _
"Provider=SQLNCLI.1;Integrated Security=SSPI;" & _
"Persist Security Info=True;Initial Catalog=Permits;" & _
"Data Source=myserver;"
To use a non-empty .odc you would need to go through a different path
after clicking "New Source - instead of "Microsoft SQL Server", choose
"Other/Advanced" then "SQL Native Client". In "Data Source", put the
server computer name, then select a database in the drop down at the
bottom. Then follow the process through. Or, you can use an empty .odc
with a connect string like
Your Database Administrator should be able to tell you which provider
you need to use. Security on SQL Server 2008 can be set up differently
for each of the two providers so you may find that one of them does not
work at all. Also, the provider has to be present on the client machine
(i.e. you need to know which providers have actually been distributed).
Peter Jamieson
http://tips.pjmsn.me.uk
On 19/01/2010 00:01, Rowena wrote:
Hi,
I am automating a mail merge from VS2008 using an exising MS Word 2008
document. The merge seems to work. The query returns the correct number of
records and some of the fields are populated but not others. For the life of
me I can't figure out why some fields aren't populated. The dates and numeric
values work, but none of the text fields are populated.
Thanks!
Dim strSQL1, strSQL2 As String
strSQL1 = ""
strSQL2 = ""
strSQL1 = "SELECT [Permit], [PermitDate], DateAdd(yyyy, 2,
[PermitDate]) AS [ExpiryDate], [ExtDate], [Description], [Lot],
[DistrictLot], [Plan], [Address], [Inspector], '250-492-0237' AS
[InspectorPhoneNumber], "
strSQL2 = "[Name], [Maddress], [MCity], [Mpostal], [Earea], [Folio],
GetDate() AS [TodaysDate] FROM [permits] WHERE [Permit]0 AND [Active]=1 AND
[FarmStatus]='N' AND [PermitDate]GetDate()-670 AND
[PermitDate]GetDate()-730"
' Create an instance of Word and make it visible.
Dim wrdApp As New Word.Application
wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
' Open document.
Dim wrdDoc As Word.Document
wrdDoc = wrdApp.Documents.Open("warning.docx")
wrdDoc.MailMerge.OpenDataSource(Name:="",
Connection:="DSN=Permits;DATABASE=Permits;uid=uid; pwd=pwd;",
SQLStatement:=strSQL1, SQLStatement1:=strSQL2)
wrdDoc.MailMerge.Execute()
' Release References.
wrdDoc = Nothing
wrdApp = Nothing