automating word mailmerge from vb.net - and word 2002 dialog popup problems

[Jason W Taylor]

My vb.net traditional client application needs to automate Word
mailmerge documents across multiple versions of Office (2000 and
later). The application comes with pre-defined .doc files that are
used as "reports". The data comes from an Access 2000 database.

Recently I have discovered that my existing code does not work as well
with Word 2002, because of problems I have seen discussed extensively
in this NG by MVP Cindy. While my code generates a mailmerged document
nicely with Word 2000 installed, I am unable to prevent display of the
"Data Link Properties" dialog if Word 2002 is installed. We are trying
to get this problem to go away, as our users will be blown away by it,
and unable to recover from the psychological damage inflicted by a
series of very technical dialogs.

None of the solutions I have seen posted have fixed my problem. One of
which was defining the SubType. This is not a viable solution anyway,
I need a backward compatible solution, and I understand the parameter
is not supported by Word 2000. We tried it anyway for fun, and it has
no effect.

Here is the VB.net code that automates the merge, based on a query of
an Access DB.

....
' Create an instance of Word
oWrdApp = CreateObject("Word.Application")
oWrdApp.Visible = False


' sTemplateFileName contains a path to a predefined merge
template
oWrdDoc =
oWrdApp.Documents.Open(FileName:=sTemplateFileName)

oWrdDoc.Select()

wrdSelection = oWrdApp.Selection()
wrdMailMerge = oWrdDoc.MailMerge()

oWrdDoc.MailMerge.OpenDataSource( [a string with the full
path to the database file get passed here], _
Connection:=sConstr, SQLStatement:="SELECT * FROM SomeTable WHERE
TableID = " & nID)

wrdMailMerge.Destination =
Word.WdMailMergeDestination.wdSendToNewDocument
wrdMailMerge.Execute(False)
....

here is what my connection string variable contains:

Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data
Source=somedatabase.mdb;Mode=Share Deny None;Jet OLEDBatabase
Password=databasepassword;Jet OLEDB:Engine Type=5;Jet OLEDBatabase
Locking Mode=1;

NOTE:

We are using a database password to secure the database and keep the
users out of it.

We have also tried an ODBC style connection string, which also causes
the Data Link Properties to rear its ugly head, but there are no
subsequent dialogs, and the merge goes on to actually work and the
merge fields are populated. This connection string looks like this:

"DSN=MS Access Database;DBQ=(full path to the .mdb file here);FIL=MS
Access;UID=Admin;PWD=databasepassword;DriverId=25;MaxBufferSize=2048;PageTimeout=5;"

From what I understand, it is not a great idea to create a dependency
on ODBC at this point.

Any thoughts or ideas? I will happily write differing code for the
existing version by detecting it in the registry in some manner, but
so far I can't get Word 2002 to work without displaying some kind of
dialogs. I Haven't even gotten through testing it to find out what
nightmares await in Office 2003.

Thanks.

Jason W. Taylor, MCSD

CODE AUTHORITY, INC
http://www.codeauthority.com
Microsoft Certified Partner

 

[Cindy M -WordMVP-]

Hi Jason,

None of the solutions I have seen posted have fixed my problem. One of
which was defining the SubType. This is not a viable solution anyway,
I need a backward compatible solution, and I understand the parameter
is not supported by Word 2000.

you're almost certainly going to need SubType to link properly with Word
2002/2003.

I assume you're using early binding in your project to the Word 9.0
library? In the VB world, if you put code that can't compile with a
particular version in a separate module it won't be compiled until run
time. I've never checked whether this approach works in the NET
environment?

The dialog box you describe has to do with the OLE DB connection method,
new in Word 2002. For an Access data source you definitely want to avoid
this. Especially if it's a source that needs to be shared with Word
2000. For one thing, as soon as your queries contain wildcards, they
won't be recognized in one or the other version.

OK, now that that's settled (well, it is for me, anyway ), the
question comes up: which connection method should you be using? It
sounds as if you currently have a DDE connection to Word 2000. And this
is what's "translating" badly to Word 2002 (1) because the SubType is
missing and 2) because the syntax is very similar to what OLE DB wants,
but isn't quite right (which is why the dialog box is appearing). Below
is some sample code Peter Jamieson put together to properly connect Word
2002 to Access using DDE.

Generally, however, I'd recommend to use ODBC (with the DSN Office
installs for the Access ODBC driver) as this doesn't involve starting up
the Access application interface.

Sub test_0100_2002()

' Connecting to an Access Table without SQL

On Error Resume Next
ActiveDocument.MailMerge.OpenDataSource _
Name:="C:\wbdb\Nordwind.mdb", _
Connection:="TABLE Bestellungen", _
subtype:=wdMergeSubTypeWord2000

' Type should be > -1 if the connection succeeded
Debug.Print ActiveDocument.MailMerge.DataSource.Type

End Sub

Sub test_0110_2002()

'Connecting to an Access Query without SQL

On Error Resume Next
ActiveDocument.MailMerge.OpenDataSource _
Name:="C:\wbdb\Nordwind.mdb", _
Connection:="QUERY Die zehn teuersten Artikel", _
subtype:=wdMergeSubTypeWord2000

' Type should be > -1 if the connection succeeded
Debug.Print ActiveDocument.MailMerge.DataSource.Type

End Sub

Sub test_0120_2002()

' Connecting to an Access Table using SQL
' With this connection type you can specify fields.
' Use [ ] or ` ` as necessary to enclose names containing spaces, "-"
and so on.

On Error Resume Next
ActiveDocument.MailMerge.OpenDataSource _
Name:="C:\wbdb\Nordwind.mdb", _
SQLStatement:="SELECT [Bestell-Nr], [Kunden-Code], Straße FROM
Bestellungen", _
subtype:=wdMergeSubTypeWord2000

' Type should be > -1 if the connection succeeded
Debug.Print ActiveDocument.MailMerge.DataSource.Type

End Sub

Sub test_0130_2002()

' Connecting to an Access Query using SQL

On Error Resume Next
ActiveDocument.MailMerge.OpenDataSource _
Name:="C:\wbdb\Nordwind.mdb", _
SQLStatement:="SELECT EinzelPreis FROM [Die zehn teuersten Artikel]",
_
subtype:=wdMergeSubTypeWord2000

' Type should be > -1 if the connection succeeded
Debug.Print ActiveDocument.MailMerge.DataSource.Type

End Sub

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail

 

[Peter Jamieson]

First, broadly speaking I agree with CIndy. But I started writing this early
this morning and it seems worth finishing.

The situation is basically as follows:
a. Word 2000 can't open using OLEDB. It may appear to use your provider
string (if you are using that when you're working with Word2K) and therefore
to be using OLEDB, but it isn't. However, you should be able to connect in
Word 2000 using

Name=""
Connection="DSN=MS Access Database;DBQ=(full path to the .mdb file
here);FIL=MS
Access;UID=Admin;PWD=databasepassword;DriverId=25;MaxBufferSize=2048;PageTim
eout=5;"
SQLStatement=a valid SQL statement that returns at least one row

which is probably what you are already doing. Notice that strictly speaking
there are some dependenices here - obviously you need ODBC, and you also
need the "MS Access Database" DSN, and it must be set up correctly, which it
normally will be by default, but none of those things is necessarily true.
b. Word 2002 will by default try to open an Access mdb using OLEDB unless
you specify a Subtype parameter. If you specify a .mdb in the Name
parameter, Word will ignore any connection string you provide when it tries
to open using OLEDB. So, because any database password you specify in that
string will be ignored, Word will fail to open the database and will present
you with the Datalink dialog. So in that case user interaction cannot be
avoided. But in any case, I have never found any combination of settings in
that dialog that will then make Word connect via OLEDB. It may be possible,
but I currently assume there is an error in Word. So the dialog box you then
see which askes for login name and password is in fact an ODBC dialog box,
and your connections are currently actually probably being achieved via
ODBC, although if you fail that dialog box, you may end up with another
password-only dialog box and connecting via DDE as I mentioned
c. So what are the options for Word 2002?

The one that I am pretty sure will work without further ado is to use
exactly the same Name (blank), Connection string and SQLStatement as above,
but specifying the Subtype as wdMergeSubtypeWord2000 which is the only way
you can get an ODBC connection with a System/User DSN to work in Word 2002.
At least, it works here, as long as there are no other complications with
System databases etc. And it also works with Word 2003 although there may be
a bit more work for you there.

So we come back to the fact that either
1. you have to know a way to specify SubType for Word 2002 but not for Word
2000 in VB.NET code. I can't help you there. Or
2. you find a way of connecting in Word 2002 that does not need a Subtype.
As we have seen, that means Name must contain something other than the name
of a .mdb, and it can't be blank, so you are going to need at least one
extra file on each Word 2002 user's system.

For (2), you can connect using ODBC with a file DSN using

Name="(full path to the .dsn file here)"
Connection="FILEDSN=(full path to the .dsn file here);"
SQLStatement=a valid SQL statement that returns at least one row

The .dsn needs to look something like:

[ODBC]
DRIVER=Microsoft Access Driver (*.mdb)
UID=Admin
PWD=databasepassword
DBQ=(full path to the .mdb file here)

Although you can put more in (the value names are the same as the ones in
the connection strings), e.g. specifying DriverID etc. if you need it. A key
point here is that the DRIVER name must be identical to the one specified in
the ODBC Administrator. If you have to distribute your code internationally
that's another thing you'll have to look out for.

However, as you might hope, you can actually reduce your .dsn to

[ODBC]
DRIVER=Microsoft Access Driver (*.mdb)

and put everything else in the Connection parameter, e.g.

Name="(full path to the .dsn file here)"
Connection="FILEDSN=(full path to the .dsn file here);DBQ=(full path to the
..mdb file here);UID=Admin;PWD=databasepassword;"
SQLStatement=a valid SQL statement that returns at least one row

So that's one way you can get everything to work with both Word 2000 and
Word 2002 (and I believe with Word 2003). And it does have the advantage
that at least you are getting the data through ODBC in both cases so
differences in the way ODBC and OLEDB operate do not enter the picture.

But suppose you would rather connect from Word 2002/2003 using OLEDB because
of the issues with ODBC you mentioned.

A .udl file seems to work:

Name="(full path to the .udl file here)"
Connection=""
SQLStatement=a valid SQL statement that returns at least one row

The .udl needs to contain

[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.Jet.OLEDB.4.0;Password=;User ID=Admin;Data Source=(full
path to the .mdb file here);Jet OLEDB: Database Password=databasepassword;

A .odc file can also be used - I suggest you try creating one with a
passwordless database and adapting it.

However, in both these cases I do not know whether or not you can leave the
password stuff out of the .udl or .odc and put it in the Connection
parameter. A few tests suggest that you cannot but I haven't tried very
hard. So if that is a security problem for you, again it's
a. stick to ODBC + file dsn or
b. reduce the visibility of the .udl/.odc by generating them "on the fly"
and disposing of them later. Probably a waste of effort or
c. find another way (perhaps using one of the other Subtype options).

But at that point I run out of steam, so I hope that gives you some
background "explanation" and some options to explore.

As a general point about ODBC, MS is certainly making it harder to install
and use the stuff you need, but it still doesn't appear to have disappeared.
If I were in your shoes I might make the simplifying assumption that since
Office 2003 /can/ work with ODBC, MS is ultimately committed to maintaining
it for that purpose at least. If and when they drop ODBC support from Office
then other approaches might become necessary.

--
Peter Jamieson - Word MVP
Word MVP web site http://www.mvps.org/word

My vb.net traditional client application needs to automate Word
mailmerge documents across multiple versions of Office (2000 and
later). The application comes with pre-defined .doc files that are
used as "reports". The data comes from an Access 2000 database.

Recently I have discovered that my existing code does not work as well
with Word 2002, because of problems I have seen discussed extensively
in this NG by MVP Cindy. While my code generates a mailmerged document
nicely with Word 2000 installed, I am unable to prevent display of the
"Data Link Properties" dialog if Word 2002 is installed. We are trying
to get this problem to go away, as our users will be blown away by it,
and unable to recover from the psychological damage inflicted by a
series of very technical dialogs.

None of the solutions I have seen posted have fixed my problem. One of
which was defining the SubType. This is not a viable solution anyway,
I need a backward compatible solution, and I understand the parameter
is not supported by Word 2000. We tried it anyway for fun, and it has
no effect.

Here is the VB.net code that automates the merge, based on a query of
an Access DB.

...
' Create an instance of Word
oWrdApp = CreateObject("Word.Application")
oWrdApp.Visible = False


' sTemplateFileName contains a path to a predefined merge
template
oWrdDoc =
oWrdApp.Documents.Open(FileName:=sTemplateFileName)

oWrdDoc.Select()

wrdSelection = oWrdApp.Selection()
wrdMailMerge = oWrdDoc.MailMerge()

oWrdDoc.MailMerge.OpenDataSource( [a string with the full
path to the database file get passed here], _
Connection:=sConstr, SQLStatement:="SELECT * FROM SomeTable WHERE
TableID = " & nID)

wrdMailMerge.Destination =
Word.WdMailMergeDestination.wdSendToNewDocument
wrdMailMerge.Execute(False)
...

here is what my connection string variable contains:

Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data
Source=somedatabase.mdb;Mode=Share Deny None;Jet OLEDBatabase
Password=databasepassword;Jet OLEDB:Engine Type=5;Jet OLEDBatabase
Locking Mode=1;

NOTE:

We are using a database password to secure the database and keep the
users out of it.

We have also tried an ODBC style connection string, which also causes
the Data Link Properties to rear its ugly head, but there are no
subsequent dialogs, and the merge goes on to actually work and the
merge fields are populated. This connection string looks like this:

"DSN=MS Access Database;DBQ=(full path to the .mdb file here);FIL=MS
Access;UID=Admin;PWD=databasepassword;DriverId=25;MaxBufferSize=2048;PageTim
eout=5;"

From what I understand, it is not a great idea to create a dependency
on ODBC at this point.

Any thoughts or ideas? I will happily write differing code for the
existing version by detecting it in the registry in some manner, but
so far I can't get Word 2002 to work without displaying some kind of
dialogs. I Haven't even gotten through testing it to find out what
nightmares await in Office 2003.

Thanks.

Jason W. Taylor, MCSD

CODE AUTHORITY, INC
http://www.codeauthority.com
Microsoft Certified Partner