Word 2007 mailmerge

[Markus]

I've had an app working for some time that allows users to create a template
word .doc file, picking fields from a table to merge from inside Word, then
using that template to generate multiple letters inside the app.

I am having problems getting this to work in Word 2007. There have been
multiple problems with the same code that has worked so well thru many
previous Word versions. I am not looking for specific advise on each problem
(yet), but general guidelines (if possible) for using mailmerge 07 over the
way it functioned in previous versions.

Can anyone outline some of the changes in mailmerge from previous versions
that I will need to consider in changing my code? Maybe someone has had to
update their app to use 07 for mailmerges and can give me some general
guidelines?

Thanks for any and all ideas on this,
Mark

 

[Peter Jamieson]

The only comments I can really provide are in the area of connectivity
and assuming your existing code works OK in Word 2003 there have been
few if any obvious changes to e.g. OpenDataSource, but numerous changes
in behaviour - some of these may be related to Vista rather than Office,
and some may even be Vista-64 "specials", but e.g.
a. it seems to be difficult to specify a DDE connection in code now
b. it seems to be difficult to specify an ODBC connection, certainly
to Excel
c. on Vista, the default folder for Data Sources is now some kind of
"search folder" that does not appear to pick up data sources recently
saved in the former default folder, i.e. the user's My Data Sources folder
d. Word will now tend to default to using the ACE provider rather than
the Jet provider it used previously, e.g. for Access, Excel, and some
delimited text file connections. (OTTOMH I couldn't point you to a
negative consequence of this, but the handling of ODBC may be one)
e. many "file type" data sources located on network drives appear to
be copied to local temp folders; if the user updates the data source in
Edit recipients, Word will typically fail when trying to save the data
source, prompting the user with the temp file name rather than the original.
f. as far as I can tell, Access/ACE has reverted to the idea that Jet
SQL is the norm and it does not appear to be possible or easy to switch
on SQL-92(?) as in previous versions - this has a potential impact on
the handling of queries with wildcard characters.
g. the XML in .odc now has a published schema and is different from
previous versions. However, Word still seems to maintain its
idiosyncratic interpretation of .odc
h. not sure .udl works any more in Vista


If your code hasn't been updated since (say) Word 2000 then there are
quite a lot more differences.


Peter Jamieson

http://tips.pjmsn.me.uk

 

[Markus]

Peter,

Many thanks for all these details. I was hoping for this kind of info.

The app has been runing fine in Word 2003 for some time, so it does not go
back that far (relatively speaking). It is only installed on XP, so no Vista
issues yet.

One problem - I am using dbf files for the merge, and I get an OLE exception
error when the opendatasource executes. At first, I thought possibly the
install of Word 07 did not automatically install odbc for these files, but
odbc for dbf seems to be there.

Your item on the difficulty in using odbc drivers (especially Excel) sounds
like possible issue for my app. Have you seen anything on any special
technique for using odbc in 07 to open a datasource?

Many thanks again,
Mark

 

[Peter Jamieson]

I guess you are the same Markus who started a thread on this subject
back on 6th March.

Can you post the code for your opendatasource call here please? It would
make it easier for me to check out a few things.

Peter Jamieson

http://tips.pjmsn.me.uk

 

[Markus]

Hi Peter,

The code is in Dbase Plus, but it has a similar format to VB. Hope you
can tell from this what the datasource issue might be. As I said, this code
has worked perfectly for years thru to Word 2003.


oMerge.OpenDataSource( cDSname_, ; // name
0, ; // Format - wdOpenFormatAuto
false, ; // ConfirmConversions
true, ; // ReadOnly
true, ; // LinkToSource
false, ; // AddToRecentFiles
"", ; // PasswordDocument
"", ; // PasswordTemplate
false, ; // Revert
"", ; // WritePasswordDocument
"", ; // WritePasswordTemplate
"DSN=dBASE Files;DBQ=" + cDSpathOnly_ + ";", ;
cDSsql )

cDSpathOnly_ = path to file e.g., C:\APPTMP
cDSname = name of dbf datasource file e.g., MM.dbf
cDSsql = sql statement e.g., Select * from MM.dbf

Many thanks again,
Mark

 

[Peter Jamieson]

I believe what is happening in this case is that
a. Word is trying to connect using the OLE DB provider
b. it either fails because that provider is not present or because the
table name is in the wrong format for the provider. The ODBC dirver
expects something like

Select * from MM.dbf

(this is what Word generates when you try to connect manually using ODBC)

but the OLE DB provider expects something more like

Select * FROM [MM]
or
Select * FROM `MM`

However, both the OLE DB provider and ODBC driver that I have here (as
it happens, it's the ACE provider/driver) accept any of

Select * FROM [MM]
Select * FROM `MM`
Select * FROM [MM.dbf]
Select * FROM `MM.dbf`

FWIW the error I see here is

"Error has occurred: The Microsoft Access database engine could not find
the object 'M.DB'. Make sure the object exists and that you spell its
name and the path name correctly."

i.e. the first and last characters of the name have been stripped.

I believe that Word will do (a) because you are specifying the .dbf
pathname in the Name parameter /and not/ specifying a Subtype parameter
equal to wdMergeSubtypeWord2000 (i.e. 8). As it happens, I thought Word
XP/2003 also did that,but perhaps the behaviour was slightly different,
or perhaps it used the OLE DB provider in those versions too, but the
unquoted file name worked in those cases. You can probably find out by
inspecting ActiveDocument.MailMerge.DataSource.connectString in Word
2003 (I wouldn't try in Word XP - it may still crash if you do that). If
the string starts with DSN, it's ODBC; if it starts with Provider, it's
OLE DB.

Here, I can get Word 2007 to connect using ODBC either by setting Name
to "", or by setting it to the pathname of the .dbf /and/ specifying the
wdMergeSubtypeWord2000 subtype value. FWIW at one time I think you had
to specify wdMergeSubtypeWord2000 in all cases where Name was set to
"", but I think an update modified that behaviour.

If it doesn't matter whether you use the ODBC provider or the OLE DB
driver, you could see if the dBase equivalent of

.OpenDataSource _
Name:="C:\wbdb\db3\KUNDNDB3.DBF", _
sqlstatement:="SELECT * FROM [KUNDNDB3]"

works on all the versions of Word you support after Word 2000.

All the above assumes that you can use the dBASE provider/driver (which
is part of Jet/ACE) and that you do not have to use the FoxPro
driver/provider, which is a different thing altogether.

FWIW the properties of the "dBASE Files" DSN here (Vista 32-bit SP1,
Word 2007 SP1) show that it is using the newer ACE driver. Modifying it
to use the older Jet driver made no difference to the behaviour of the
OpenDataSource code I tried here.

Peter Jamieson

http://tips.pjmsn.me.uk

 

[Markus]

Peter,

You are the best! Many thanks for all the details. Have not been able to
try this out yet, but the details you outline should help me in tracking down
the issue.

Thanks again, and I'll post back here after I confirm a solution.
Mark

I believe what is happening in this case is that
a. Word is trying to connect using the OLE DB provider
b. it either fails because that provider is not present or because the
table name is in the wrong format for the provider. The ODBC dirver
expects something like

Select * from MM.dbf

(this is what Word generates when you try to connect manually using ODBC)

but the OLE DB provider expects something more like

Select * FROM [MM]
or
Select * FROM `MM`

However, both the OLE DB provider and ODBC driver that I have here (as
it happens, it's the ACE provider/driver) accept any of

Select * FROM [MM]
Select * FROM `MM`
Select * FROM [MM.dbf]
Select * FROM `MM.dbf`

FWIW the error I see here is

"Error has occurred: The Microsoft Access database engine could not find
the object 'M.DB'. Make sure the object exists and that you spell its
name and the path name correctly."

i.e. the first and last characters of the name have been stripped.

I believe that Word will do (a) because you are specifying the .dbf
pathname in the Name parameter /and not/ specifying a Subtype parameter
equal to wdMergeSubtypeWord2000 (i.e. 8). As it happens, I thought Word
XP/2003 also did that,but perhaps the behaviour was slightly different,
or perhaps it used the OLE DB provider in those versions too, but the
unquoted file name worked in those cases. You can probably find out by
inspecting ActiveDocument.MailMerge.DataSource.connectString in Word
2003 (I wouldn't try in Word XP - it may still crash if you do that). If
the string starts with DSN, it's ODBC; if it starts with Provider, it's
OLE DB.

Here, I can get Word 2007 to connect using ODBC either by setting Name
to "", or by setting it to the pathname of the .dbf /and/ specifying the
wdMergeSubtypeWord2000 subtype value. FWIW at one time I think you had
to specify wdMergeSubtypeWord2000 in all cases where Name was set to
"", but I think an update modified that behaviour.

If it doesn't matter whether you use the ODBC provider or the OLE DB
driver, you could see if the dBase equivalent of

.OpenDataSource _
Name:="C:\wbdb\db3\KUNDNDB3.DBF", _
sqlstatement:="SELECT * FROM [KUNDNDB3]"

works on all the versions of Word you support after Word 2000.

All the above assumes that you can use the dBASE provider/driver (which
is part of Jet/ACE) and that you do not have to use the FoxPro
driver/provider, which is a different thing altogether.

FWIW the properties of the "dBASE Files" DSN here (Vista 32-bit SP1,
Word 2007 SP1) show that it is using the newer ACE driver. Modifying it
to use the older Jet driver made no difference to the behaviour of the
OpenDataSource code I tried here.

Peter Jamieson

http://tips.pjmsn.me.uk

Hi Peter,

The code is in Dbase Plus, but it has a similar format to VB. Hope you
can tell from this what the datasource issue might be. As I said, this code
has worked perfectly for years thru to Word 2003.


oMerge.OpenDataSource( cDSname_, ; // name
0, ; // Format - wdOpenFormatAuto
false, ; // ConfirmConversions
true, ; // ReadOnly
true, ; // LinkToSource
false, ; // AddToRecentFiles
"", ; // PasswordDocument
"", ; // PasswordTemplate
false, ; // Revert
"", ; // WritePasswordDocument
"", ; // WritePasswordTemplate
"DSN=dBASE Files;DBQ=" + cDSpathOnly_ + ";", ;
cDSsql )

cDSpathOnly_ = path to file e.g., C:\APPTMP
cDSname = name of dbf datasource file e.g., MM.dbf
cDSsql = sql statement e.g., Select * from MM.dbf

Many thanks again,
Mark