OpenDataSource Problem in Word 2002 onwards

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Slartibartfast - 29 Jan 2004 17:06 GMT

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.

Reply to this Message

Peter Jamieson - 29 Jan 2004 23:02 GMT

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.

--
Peter Jamieson - Word MVP
Word MVP web site http://word.mvps.org/

> Hi all
>
[quoted text clipped - 29 lines]
> Format:=wdOpenFormatAuto, _
> Connection:="Provider=MSDASQL.1;Persist Security Info=True;Extended

Properties=""DSN=Affinis1;Description=Affinis1;UID=Administrator;APP=Microso
ft

> Office XP;WSID=IPCDEVELOPER;DATABASE=Affinis;Trusted_Connection=Yes"";Initial
> Catalog=Affinis", _
[quoted text clipped - 10 lines]
>
> Any help gratefully received.

Reply to this Message

Slartibartfast - 30 Jan 2004 12:11 GMT

> You need an extra paramter in the Word 2002/2003 call - it is
> Subtype:=wdMergeSubtypeWord2000. It isn't document well (if at all), but
[quoted text clipped - 8 lines]
> Peter Jamieson - Word MVP
> Word MVP web site http://word.mvps.org/

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

Reply to this Message

Peter Jamieson - 30 Jan 2004 12:42 GMT

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

--
Peter Jamieson - Word MVP
Word MVP web site http://word.mvps.org/

> > You need an extra paramter in the Word 2002/2003 call - it is
> > Subtype:=wdMergeSubtypeWord2000. It isn't document well (if at all), but
[quoted text clipped - 18 lines]
>
> SB

Reply to this Message

Slartibartfast - 02 Feb 2004 08:57 GMT

> 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
[quoted text clipped - 10 lines]
> 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

Reply to this Message

Hubajube - 10 Mar 2004 14:35 GMT

Hi, I just came across this thread in Google. Am I correct in assumin
that you cannot use OpenDataSource with a direct connection instead o
a DSN?

I wanted to use a connection string something like this:

Provider=SQLOLEDB.1;User ID=user;Password=pass;Initial Catalog=db;Dat
Source=SERVER;Integrated Security=SSPI;

instead of having to set a DSN

Reply to this Message

Peter Jamieson - 11 Mar 2004 15:23 GMT

The general rule with OpenDataSource is that it always wants to connect to
or via some form of external file, except in the case where you are
connecting using ODBC using a User or System DSN.

Since you're trying to connect using OLEDB, you will need either a .udl file
or a .odc file. There's simply no way to connect to a server-type DBMS
without one as far as I know. If you specify a .odc, you can /probably/
specify a completely empty .odc and put all the connection info. in the
Connection string - this seemed to work in Word 2002 but it's obviously not
how it is supposed to work and I wouldn't count on it too much.

Signature

Peter Jamieson - Word MVP
Word MVP web site http://word.mvps.org/

> Hi, I just came across this thread in Google. Am I correct in assuming
> that you cannot use OpenDataSource with a direct connection instead of
[quoted text clipped - 9 lines]
> ------------------------------------------------
> ~~ Message posted from http://www.WordForums.com/

Reply to this Message