From what you describe, I'd guess you are probably actually using the OLEDB
provider, not the ODBC driver. Are you asked to save a .odc file at some
point? If so, it's OLEDB.
Unfortunately I no longer have an Oracle set-up to experiment with here and
the following is rather complicated, but this is what I would probably do:
a. if my connection used a .odc file, open the .odc file using Notepad and
look at the connection string. (Note that it is in an "HTML" format). Does
it contain your login information? If it does not, try editing the file to
include the login information and try again. If it does, then this approach
is probably not going to work. You can try to open the data source using a
VBA OpenDataSource statement instead. In that case, I would
- copy the connection string and de-HTML it. The resulting string needs to
have a maximu length of 255 characters. If you need to shorten it, there
are usually plenty of parameters you do not need to set
- use Notepad to create a completely empty .odc file, e.g. called
blank.odc. Let's suppose its pathname is c:\myodcs\blank.odc
- create a Word VBA macro that calls OpenDataSource, e.g.
Sub OpenOracle()
ActiveDocument.MailMerge.OpenDataSource _
Name:="c:\mydocs\blank.odc", _
Connection:="the connection string you took from the .odc", _
SQLStatement:="SELECT * FROM tablename"
End Sub
You need to modify the SQL to suit your application.
Make you mail merge main document your active document, run the macro, and
see what happens.
b. If my guess is wrong and you are using ODBC, did you try OLEDB?
c. If you cannot make OLEDB work or were trying to use ODBC anyway, I
suspect that you will need to use VBA anyway, because login security info.
is not normally retained when you create ODBC connections however you do it.
To use ODBC, you will need an ODBC DSN for your Oracle connection - in
recent versions of Windows you can create one in Control
Panel|Administrative Tools||ODBC Administrator. Normally you need a User or
System DSN, but it can be useful to create a File DSN because you can open
the resulting DSN in Notepad and use the contents to help you create a
connection string for Word.
Once you have created your User/System DSN, there are two ways you can try
to connect:
- go through the Select Data Source dialog, and go via MS Query (it's in
the Tools menu at the top right of the dialog). Follow the steps in there.
Before you return the data to Word, I suggest you save the query as a .dqy
file as that can also be opened in Notepad and give you information about
what should be in the connection string. My guess is that if this works, you
will see the data when it is first returned, but after that you will see
errors because the login security information has been discarded.
- use VBA. In this case you will need to construct your own connection
string. The first part needs be
DSN=dsnname;
where "dsnname" is the name of the user/system DSN
the rest can be constructed either using the info. in the .dqy or in the
..dsn file. Each parameter needs to be followed by a semicolon.
Then try
Sub OpenOracle()
ActiveDocument.MailMerge.OpenDataSource _
Name:="", _
Connection:="DSN=dsnname;the rest of the connection string you
constructed", _
SQLStatement:="SELECT * FROM tablename", _
Subtype:=wdMergeSubtypeWord2000
End Sub
I hope at least one of those approaches gets you somewhere. If not, another
thing you can try if you happen to have Access is to use Access to link to
your Oracle table/.view, and use the resulting Access table as your
datasource.
Peter Jamieson
