Database field merge to a link table in Oracle

[Bao Le]

When I do a mail merge in an Access table using DATABASE
field, everything works fine. But when I try to merge to
a link table in Oracle I got the following error:

"Word was unable to open the data source"

Can someone tell me if I can do this? I'm using WORD 97.

Thanks,
Bao

 

[Peter Jamieson]

My guess is that it is a security problem.

I don't have this particular combination available to test here at the
moment, but I do have an Access .mdb linked to an MS SQL Server table.

I have a Word 97 DATABASE field that inserts a .mdb table which is a link to
a SQL Server table. When I link to the SQL Server table from Access , I can
a. use an ODBC DSN which specifies "Integrated Security". In this case,
neither Access nor Word have to specify a password to get the SQL Server
data. The Word DATABASE field works. However, I do not think you have this
option with Oracle.
b. use an ODBC DSN whcih does not specify "Integrated Security", but
specify that I want to remember the Oracle password when I make the link
from Access to Oracle.. When I make the link to the table from Access,
Access asks me whether I want to save the password. The Word DATABASE field
works.
c. use an ODBC DSN whcih does not specify "Integrated Security", and
specify that I do not want to remember the Oracle password when I make the
link from Access to Oracle. In this case, the Word database field will only
work if Word connects to Access using DDE. In that case, Word will start
Access. Access will ask for the Oracle password. Once you have supplied it,
Access can get the data and provide it to Word. If Word connects using ODBC,
Word does not open Access. Word does not know the password, Word does not
prompt for the password, so the DATABASE field does not work.

If your table is linked as in option (b), then I do not know what is wrong.
If your table is linked as in option (c), then you will need to do one of
the following:
a. change the way the table is linked in Access (i.e. tell Access to
remember the password)
b. change the way Word links to Access to use DDE, so that Access asks you
for the password
c. connect your DATABASE field directly to the Oracle database (you will
probably need to specify the user name and password in the DATABASE field
itself). I do not think you will be able to specify the necessary password
in the DATABASE field if you connect Word to Access using ODBC.

 

[Bao Le]

Hi Peter

I've been trying to do a WORD merge with the Oracle table in the following way using the DATABASE field

a) Using system DSN

{ DATABASE \c "DSN=gard; UID=userid;PWD=password" \s "SELECT PLN_ORGN_DSCRPTN FROM AMS_REF.PLAN_ORIGIN" \* CharFormat

b) without using DS

{ DATABASE \c "DRIVER={Microsoft ODBC for Oracle};UID=userid;PWD=password;SERVER=gard;" \s "SELECT PLN_ORGN_DSCRPTN FROM AMS_REF.PLAN_ORIGIN" \* CharFormat

When I ran the merge using option (a), it worked fine merging data directly to the Oracle table. However when I ran option (b) I got a "field calculation error occurred in record 1". I'm not sure if I'm missing anything in the connection string

Thanks
Bao

 

[Peter Jamieson]

Word seems to be able to set up and use "DSN-less" connections (e.g. your
option (b) ) in some (limited) circumstances.

However, I have never managed to set up a DSN-less connection using the \c
parameter in the DATABASE field or the OpenDataSource method in VBA.
Although I like to think I try all the possibilities,
a. there is not much documentation in this area. I have never seen official
documentation that says that DSN-less connections are not supported by Word.
b. I cannot be sure it is impossible.

So if you ever manage to get it to work, please let us know how!

If it is more useful to you, you should be able to specify a file DSN using
FILEDSN=<the pathname of the file DSN>. However, that may start failing in
Word 2002 and later.

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

Hi Peter,

I've been trying to do a WORD merge with the Oracle table in the following way using the DATABASE field:

a) Using system DSN:

{ DATABASE \c "DSN=gard; UID=userid;PWD=password" \s "SELECT

PLN_ORGN_DSCRPTN FROM AMS_REF.PLAN_ORIGIN" \* CharFormat }

b) without using DSN

{ DATABASE \c "DRIVER={Microsoft ODBC for

Oracle};UID=userid;PWD=password;SERVER=gard;" \s "SELECT PLN_ORGN_DSCRPTN
FROM AMS_REF.PLAN_ORIGIN" \* CharFormat }

When I ran the merge using option (a), it worked fine merging data

directly to the Oracle table. However when I ran option (b) I got a "field
calculation error occurred in record 1". I'm not sure if I'm missing
anything in the connection string?