Word was unable to open the data source

[Todd]

Hi,

I'm trying to integrate a mail-merge into our inventory control
application, but without success.

I'm using Word 2002 and the data is coming from SQL Server 2k.

The application is a C# windows forms app.

Basically I'm getting an exception "Word was unable to open the data
source" when I make a call to MailMerge.OpenDataSource.

The frustrating thing is that it works fine when I do it myself in
Word. I use exactly the same .ODC file (created by Word) in the
application. The SQL queries work fine on their own too.

I've tried recording a macro, then translating the VBA into C#, but to
no avail. It really is just a couple of function calls - arghhh!

I think I've tried just about every conceivable parameter combination
but without success. Using http://support.microsoft.com/?kbid=285332
as a guide, the code is as follows:



private void DoMailMergeToPrinter
(
object oTemplate, // path\filename of the Word envelope template
string datasource, // path\filename of the .odc file created by
Word
object query // SQL query string
)
{
object oFalse = false;
object oTrue = true;
object emptyString = "";
object missing = System.Reflection.Missing.Value;

Word.ApplicationClass oWordApp = new Word.ApplicationClass ();
Word.Selection wrdSelection;
Word.MailMerge wrdMailMerge;
Word.MailMergeFields wrdMergeFields;

try
{
Word.Document oWordDoc = oWordApp.Documents.Add(ref oTemplate, ref
missing,ref missing, ref missing);
oWordDoc.Activate ();

wrdMailMerge = oWordDoc.MailMerge;
wrdSelection = oWordApp.Selection;
wrdMergeFields = wrdMailMerge.Fields;

wrdMailMerge.MainDocumentType =
Word.WdMailMergeMainDocType.wdEnvelopes;
wrdMailMerge.OpenDataSource (datasource, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing, ref missing, ref
missing, ref missing, ref missing, ref missing,
ref query,
ref missing, ref missing, ref missing);
wrdMailMerge.SuppressBlankLines = true;
wrdMailMerge.Destination =
Word.WdMailMergeDestination.wdSendToPrinter;
wrdMailMerge.Execute (ref oFalse);
}
catch (Exception ex)
{
MessageBox.Show (this, ex.Message, "Exception",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
oWordApp.Application.Quit (ref oFalse, ref missing, ref missing);
}
}


I thought perhaps it might be a security issue, but I've tried logging
in as "sa" to no avail. I'm using the same .ODC file anyway, so if it
works manually, it should also work when automated.

Please help!

Thank you,
Todd.

 

[Peter Jamieson]

Two things you could look at:
a. Word seems to be quite fussy about the SQL syntax - queries that work
elsewhere may fail in Word. In particular, you seem to have to use table
alias names to qualify column names even where it is not strictly necessary.
b. Although the article you refer to says that Word does not use the
connection parameter when connecting via OLEDB, in my experience you can fix
some problems (at least for the time being - MS may have plugged this
particular loophole) by using a completely empty .odc file and specifying
the necessary connection parameters in the OpenDataSource call. Yu still
need to specify the .odc in the Name parameter and the SQL statement, but
you may find this works where the other approach does not.

Something I tend to do just as a sanity check is double-click on the .odc
file (which is in fact an HTML file that uses some "behaviours" defined in
DATACON.HTC when you display it) and display it in IE. If there are any
security issues, they will probably appear there. In particular, if you are
/not/ using SQL Server integrated security, the SQL Server user name and
password will have to be recorded in the .odc, or in the connection string
in OpenDataSource if you use the approach suggested in (b)

 

[Todd]

Hi Peter,

Thanks very much for your quick response. I actually got it to work
after much mucking about with some help from an earlier posting of
yours - "Re: SQL Mail Merge Can't Open Data Source". I think the
problem was the login being used with ODC file. I pass the .odc to
OpenDataSource and leave connect string and
SQL statement blank. Seems to work fine now.

BTW, when I view an .odc in IE, all I see is the name of the .ODC file
and the comment I added to it when I created it with Word. Should I
see records as well? If so, why would they not be showing up?

Thanks again,
Todd.

 

[Peter Jamieson]

BTW, when I view an .odc in IE, all I see is the name of the .ODC file

and the comment I added to it when I created it with Word. Should I
see records as well? If so, why would they not be showing up?

It does seem to depend on the data source and exactly what you have
specified when creating the .odc (for example, if you haven't specified a
table or view in the .odc, you should see a list of tables/sources in the
specified/default database. If you aren't using integrated security you may
need to log on).

However, there is some factor I haven't yet been able to work out. e.g. I
have two .odc s here, getting table data from the same server (but different
databases), apparently with identical connection strings. One displays
records, the other does not.. So there must be a difference somewhere - I
just can't see it. But if I select the file name in Windows explorer, right
click and choose Analyze in Excel, Excel displays the table data in both
cases.