Mail Merge Word 2003, VB.NET 2008, CreateDataSource

[che]

Hi there,

I am trying to programmatically create an .ODC in my application and assign
it to a template for mail merge.
So far I have recorded a macro setting the .ODC up and cut and paste the
macro code into my application:

myDoc.MailMerge.OpenDataSource(Name:= _
"C:\Documents and Settings\cb\My Documents\My Data
Sources\RRS-MSSQL-DEV SUPPORT ctrl_users.odc" _
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=WdOpenFormat.wdOpenFormatAuto, Connection:= _
"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=True;Initial Catalog=SUPPORT;Data Source=RCS-MSSQL-DEV;Use Procedure for
Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=CLYD42J;Use
Encryption for Data=False;Tag with column" _
, SQLStatement:="SELECT * FROM ""ctrl_users""", SQLStatement1:="", _
SubType:=WdMergeSubType.wdMergeSubTypeOther)

This isn't working like it does in Word itself. I only have 2 database
fields after the datasource is opened - M_1 and M! When I complete this macro
in Word itself, I have access to all the correct fields.

Any help would be appreciated. Thanks in advance!

 

[Peter Jamieson]

I only have 2 database

fields after the datasource is opened - M_1 and M

NB this is typical behaviour when Word cannot connect to the real data
source via a .odc. The question is what's preventing it in this case.

What I was not completely sure about from your description is whether you
have managed to get the recorded macro to work in Word VBA, or whether you
can only make the connection when you set it up manually, and also whether
you are using exactly the same .odc in all your tests.

I cannot see anything obviously wrong with your OpenDataSource statement
except that the Connection string is truncated (that's something that Word
gets wrong) and that may or may not cause a problem.

In VBA, I would try one of the following:

If your .odc file specifies the table or view that you need, try...

myDoc.MailMerge.OpenDataSource _
Name:="C:\Documents and Settings\cb\My Documents\My Data
Sources\RRS-MSSQL-DEV SUPPORT ctrl_users.odc", _
Connection:="", _
SQLStatement:="", _
SubType:=wdMergeSubTypeOther

(you should not need any of the other stuff, and you may be able to get away
with just the Name parameter).


Or you can try the following, even with a completely empty .odc file:

myDoc.MailMerge.OpenDataSource _
Name:="C:\Documents and Settings\cb\My Documents\My Data
Sources\RRS-MSSQL-DEV SUPPORT ctrl_users.odc", _
Connection:="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=SUPPORT;" _
& "Data Source=RCS-MSSQL-DEV;", _
SQLStatement:="SELECT * FROM [ctrl_users]", _
SubType:=wdMergeSubTypeOther

If the problem is that VBA connects OK, but the connection is not made when
you use the equivalent code in VBA.NET, that's probably not going to be
enough.