Mail Merge Problem using InsertDatabase in VB6 & Word 2007

[Ron-Developer]

Hi, I seem to have run into a problem with Word 2007. The simplified scenario
and extracted code below works for all other versions of Word, but throws up
warnings in Word 2007:

I have a table in an Access database (c:\reports.mdb) with the following
fields: ID, FirstName, Surname

In VB 6 I have the following code to insert a table of all the first names
and surnames into one word letter:

Call m_wrddocWordDocument.Range(m_wrdRange.Start,
m_wrdRange.End).InsertDatabase(Format:=0, Style:=0, LinkToSource:=False, _
Connection:="DSN=Reports", SQLStatement:="Select FirstName, Surname
from Adresses", _
PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="" _
, WritePasswordTemplate:="", DataSource:="C:\Reports.mdb", _
From:=-1, To:=-1, IncludeFields:=False)

When the code executes I get an "Invalid Merge Field" error for the ID field
in my database. The detail of the error is "This merge field is used in the
main document, but it does not exist in the data source". I have a choice to
remove the invalid field from the main document or to replace it with a valid
merge field (the list of valid merge fields includes just those in the select
statement above). This has to be done for every field omitted from the select
statement, but existing in the base table in Access. When the document
finally shows the table is skewed, with the ID column included with dud data.

As mentioned, this works perfectly for all other versions of Word. Can
anyone indicate why this might not be the case for Word 2007?

 

[Peter Jamieson]

1. This problem occurs here on Word 2007 running on Vista 64 (I don't
/think/ the O/S version is significant but working with ODBC on Vista 64
introduces additional problems so it could be)

2. FWIW a similar problem also occurred sometimes with earlier versions of
Word (probably Word 2002 onwards), at least when you actually inserted a
DATABASE field. However, I have never been able to pin down what exactly
causes it and the specific example you give does not seem to cause a
problem.

3. What seems to fix the problem here is to change the query syntax to
include a table alias, e.g.

"Select a.FirstName, a.Surname from Adresses a"

4. That will probably work for earlier versions as well (I'm assuming you're
trying to maintain a single version of your source) but I would check.
Another possibility which I am unable to check here is that using a file DSN
might work (if it does, it's probably for the same reason that you can no
longer use machine DSNs in DATABASE fields because they do not work with a
nonblank \d parameter). Alternatively, you could report the problem to
Microsoft support (if you do, mention my name).

5. When you issue other data source-related commands from VBA, e.g.
OpenDataSource, that syntax is only necessary when you use OLE DB to open
the data source. Syntactically it should not be needed, but I think either
Word, or the "Office Data Source Object" (ODSO) that Word uses internally to
open OLE DB data sources, does something wrong. But this fact leads me to
suspect that Word 2007 is actually trying to use OLE DB (possibly even using
the OLE DB provider for ODBC data sources) to open the data source in this
case, whereas perhaps earlier versions were using ODBC directly. FWIW
another thing I notice is that if you try to use "SELECT * FROM addresses"
in your InsertDatabase call, you (probably) see an error that the table
"ddresse" could not be found, which suggests that some code somewhere could
be blindly expecting table names to be surrounded by quote or square bracket
delimiters and stripping off the leading and trailing characters come what
may. I also get the impression that one part of the code is using the SELECT
string as provided to get the data, but another part of the code is filling
in the column names by telling the provider to get the column information
from the table "addresses". But that's pure speculation on my part.

 

[Ron-Developer]

Hi Peter,

The OS isn't significant since I have been able to reproduce the problem on
Windows XP SP2 and Server 2003.

Using the alias worked! It also works in Word 2000 and 2003.

In my live code I was already using "[" and "]" to surround table and field
names.

I agree with your speculation in the last point 5, in that I think it is
using the table name in the select and then retrieving all of the columns
(ignoring the specified fields), and then trying to merge all of them, even
those fields I didn't specify.

I have recommended my customers to stay away from Office 2007 until at least
SP1. There doesn't seem to be a release date for that yet (or a fix list).
Could you point me in the best direction so I can raise the awareness on this
so a fix can get included?

Thanks for the help Peter.

Ronan

 

[Peter Jamieson]

Glad the alias thing worked.

Could you point me in the best direction so I can raise the awareness on
this
so a fix can get included?

The only way I have ever been able to do this in the UK is to phone
Microsoft "Professional Support" and have them create a support incident.
It's simpler for them if you can provide step-by-step instructions to
reproduce the error.

In theory you can submit an incident via e-mail (if you have a look around
the Microsoft support.microsoft.com site you may be able to find the
facility that lets you do that. In practice I have had great difficulty
using that route.

I wish there was a simpler way to "bring the problem to someone's attention"
reliably but if there is, I do not know what it is.

--
Peter Jamieson
http://tips.pjmsn.me.uk

Hi Peter,

The OS isn't significant since I have been able to reproduce the problem
on
Windows XP SP2 and Server 2003.

Using the alias worked! It also works in Word 2000 and 2003.

In my live code I was already using "[" and "]" to surround table and
field
names.

I agree with your speculation in the last point 5, in that I think it is
using the table name in the select and then retrieving all of the columns
(ignoring the specified fields), and then trying to merge all of them,
even
those fields I didn't specify.

I have recommended my customers to stay away from Office 2007 until at
least
SP1. There doesn't seem to be a release date for that yet (or a fix list).
Could you point me in the best direction so I can raise the awareness on
this
so a fix can get included?

Thanks for the help Peter.

Ronan