OpenDataSource Problem in Word 2002 onwards

[Slartibartfast]

Hi all

I have a set of macros designed in Word 2000 to do automated Mail
Merges from a SQL server database via ODBC. They work very well at the
moment. These now need to be ported to Word 2003, however, I cannot
seem to get the OpenDataSource command to connect to a datasource in
Word 2002/3. The old code left the name field blank and used a system
DSN which no longer seems to be allowed.

The new code I obtained by recording a test macro - but it only
partially works. If I manually connect the document to the data source
before running the macro then it runs okay, if I don't manually
connect it always fails on the OpenDataSource line. This is
unacceptable since the system needs to run without any intervention.

Any ideas why this OpenDataSource fails to actually connect to the
data source? It's driving me nuts.

New Connect:

ActiveDocument.MailMerge.OpenDataSource Name:= _
"Q:\live\DataSources\Affinis.odc", _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:="", _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="Provider=MSDASQL.1;Persist Security Info=True;Extended
Properties=""DSN=Affinis1;Description=Affinis1;UID=Administrator;APP=Microsoft
Office XP;WSID=IPCDEVELOPER;DATABASE=Affinis;Trusted_Connection=Yes"";Initial
Catalog=Affinis", _
SQLStatement:="SELECT * FROM ""IPCTestView"" where
CorrespondenceItemID=324805", _
SQLStatement1:="", _
SubType:=wdMergeSubTypeOther

Old Connect (word 2000):

ActiveDocument.MailMerge.OpenDataSource Name:="", _
Connection:="DSN=affinis1;", _
SQLStatement:=MergeSQL$

Any help gratefully received.

 

[Peter Jamieson]

You need an extra paramter in the Word 2002/2003 call - it is
Subtype:=wdMergeSubtypeWord2000. It isn't document well (if at all), but
this particular value makes Word follow the same execution path as Word
2000. Otherwise, Word 2002/2003 requires a non-blank Name parameter. Your
existing DSN etc. should be OK, but it's possibel that the SQL syntax may
need to change a bit - if your OpenDataSOurce doesn't work witht he new
parameter, I'd simplify your query right down to "SELECT * FROM tablename"
and ensure that works before going any further.

 

[Slartibartfast]

You need an extra paramter in the Word 2002/2003 call - it is
Subtype:=wdMergeSubtypeWord2000. It isn't document well (if at all), but
this particular value makes Word follow the same execution path as Word
2000. Otherwise, Word 2002/2003 requires a non-blank Name parameter. Your
existing DSN etc. should be OK, but it's possibel that the SQL syntax may
need to change a bit - if your OpenDataSOurce doesn't work witht he new
parameter, I'd simplify your query right down to "SELECT * FROM tablename"
and ensure that works before going any further.

Thanks very much for this - it works a treat. Saved my sanity and my
hair!

A question though. How futureproof is this as a method? Presumably the
mandatory name field in Word 2002/3 is to address some issue? . Is
there a new recommended approach to connecting a macro to an ODBC
datasource in word 2003?

SB

 

[Peter Jamieson]

A question though. How futureproof is this as a method? Presumably the

mandatory name field in Word 2002/3 is to address some issue? .

As I understand it, at least some calls to OpenDataSource are now by default
routed through ODSO (Office Data Source Object), which is basically geared
towards OLEDB data sources and the use of either a file type data source
(.dbf, .doc etc.) or the OLEDB/ODSO equivalents of .dsn files, i.e. .udl
files or .odc files. I suspect the change in OpenDataSource reflects a
design decision in ODSO that it must have a file to open. But that is mainly
speculation on my part. In principle, because the OLEDB provider for ODBC
data sources is the default OLEDB provider, an ODBC connection string ought
to contain enough information for Word to connect using that provider, but I
have yet to manage it (I haven't tried hard!) and on the whole do not
believe many interfaces are as transparent as they are often claimed to be
(i.e. the behaviour is likely to be different from the "ODBC only"
behaviour).

Is
there a new recommended approach to connecting a macro to an ODBC
datasource in word 2003?

I can't tell you how futureproof it is, but the same calls generally seem to
work in Word 2003 except that there is now more interference from security
popups etc. You could also consider connecting via a file DSN (put the DSN
pathname in Name and use "FILEDSN=the dsn pathname" instead of "DSN=the
machine dsn name" in the Connection parameter). I don't even know how
futureproof using ODBC is - as with so many things, it was supposedly
superseded by OLEDB many years ago (and OLEDB has in principle been
superseded by whatever the equivalent in .NET is). In principle I suppose
you should move to OLEDB, but IME ODBC is still a usable interface, in some
respects it arguably works better than OLEDB, there is a properly defined
system of "escapes" which provide at least some help if your data source
changes from one DBMS to another (only available in OLEDB if the specific
providerhas decided to support it), and there never seems to have been an
OLEDB equivalent to MS Query which is an ODBC-only piece of software.

 

[Slartibartfast]

I can't tell you how futureproof it is, but the same calls generally seem to

work in Word 2003 except that there is now more interference from security
popups etc. You could also consider connecting via a file DSN (put the DSN
pathname in Name and use "FILEDSN=the dsn pathname" instead of "DSN=the
machine dsn name" in the Connection parameter). I don't even know how
futureproof using ODBC is - as with so many things, it was supposedly
superseded by OLEDB many years ago (and OLEDB has in principle been
superseded by whatever the equivalent in .NET is). In principle I suppose
you should move to OLEDB, but IME ODBC is still a usable interface, in some
respects it arguably works better than OLEDB, there is a properly defined
system of "escapes" which provide at least some help if your data source
changes from one DBMS to another (only available in OLEDB if the specific
providerhas decided to support it), and there never seems to have been an
OLEDB equivalent to MS Query which is an ODBC-only piece of software.

Once again, thank you.

SB