Re: Word 2007 mailmerge

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 wrote:
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" wrote:

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 wrote:
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" wrote:

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 wrote:
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
.

Relevant Pages