Word to Oracle via VBA
Ellie Smarandache
Word 2002 document. At the opening time I will enter a work request
number. Then Word, without asking anything else from me, should be
able to connect to an existing Oracle database, go to a specific table
in that database, retrieve that work request number and bring back a
few other fields for that work request. It should display those fields
in the open Word document. Then I should be able to type some more on
that document, print and save it.
I am having problems, right from the beginning: connecting from Word
to Oracle. The connection string I would use in Access - VBA to
connect to Oracle just does not work in Word.
Any help is appreciated. Thank you.
Nuno Souto
>
> I am having problems, right from the beginning: connecting from Word
> to Oracle. The connection string I would use in Access - VBA to
> connect to Oracle just does not work in Word.
>
> Any help is appreciated. Thank you.
You need to have an ODBC driver for Oracle
and to install Oracle Client and Oracle Objects for OLE.
Then it should be like any other ODBC connection:
give the connection (in the control panel) a uid/pwd
and a database to connect to. And use the
ODBC connection name in your VBA.
I haven't tried it in Word, but it works without a hitch
in Excel.
Cheers
Nuno Souto
wizo...@yahoo.com.au.nospam
Mark Townsend
Smarandache at esmara...@tucsonelectric.com wrote on 4/7/03 11:16 AM:
> I need to put together some code that will allow me to open a certain
> Word 2002 document. At the opening time I will enter a work request
> number. Then Word, without asking anything else from me, should be
> able to connect to an existing Oracle database, go to a specific table
> in that database, retrieve that work request number and bring back a
> few other fields for that work request. It should display those fields
> in the open Word document. Then I should be able to type some more on
> that document, print and save it.
Hmm - instead of VBA et al, why not define the work request as an XML doc to
the DB, then use Word to edit normally. You can also use SQL to search the
docs. Needs XML DB in 9.2, but works a treat - see the Oracle by Example at
http://otn.oracle.com/products/oracle9i/htdocs/9iober2/obe9ir2/default_otn.h
tm#
Niall Litchfield
driver for Oracle (I'd choose Oracle's but it doesn't matter to get the
thing operational) *or* Oracle Objects for OLE. These are alternative access
technologies. There is a third technology OLEDB which in fact is the method
I'd choose above either of the others
code snippet follows (tested on word XP) demonstrating returning a recordset
and using the oledb provider.
Sub test()
'
' test Macro
' Macro created 08/04/2003 by Niall Litchfield
'
Dim cn As New Connection
Dim dt As Date
Dim rs As Recordset
With cn
.ConnectionString = "Provider=Oraoledb.oracle;data source=<tnsnames
entry>;user id=<username>;password=<password>;"
.Open
End With
Set rs = cn.Execute("select sysdate from dual")
dt = rs.Fields(0).Value
Selection.InsertAfter dt
End Sub
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Nuno Souto" <wizo...@yahoo.com.au> wrote in message
news:73e20c6c.03040...@posting.google.com...
Nuno Souto
> I don't think that that is quite correct Nuno. You need *either* an ODBC
> driver for Oracle (I'd choose Oracle's but it doesn't matter to get the
> thing operational) *or* Oracle Objects for OLE. These are alternative access
> technologies. There is a third technology OLEDB which in fact is the method
> I'd choose above either of the others
Isn't OLEDB the "successor" to OO4OLE? I thought it was.
Just had a look at the setup in the office and it's
Excel only with ODBC. Then it's got the Excel database query pack
installed. This activates the "Get External Data" option in
Data menu. And the "Run Database Query" option. There appears to be
no need for anything other than ODBC, at least in Excel. I've got
OO4OLE installed as well, but it's an old thing.
I'd expect Word to also be able to work with only ODBC but as you say,
it's better to use OLEDB. At least it simplifies the VBA code.
>
> code snippet follows (tested on word XP) demonstrating returning a recordset
> and using the oledb provider.
Thanks. Like I said, I use Exel97. I can't really provide detailed
advice on later versions of Word. Your example looks very easy,
I must try this out. Would come in very useful. Thanks a lot.
Cheers
Nuno Souto
wizo...@yahoo.com.au.nospam
Gabriel Gonzalez
Maybe, but not a "replacement." oo4o is Oracle's own access library, and it
does not necessarily follow any standard. It's just an OCX component one
can talk to. ODBC, OLEDB, and now .NET Data Providers _are_ standards, and
Oracle drivers for those data access methods do follow the standard
interface.
In other words, oo4o was created non-standard because the existing data
access methods were too simple back then. Now with the better access
methods and more mature drivers in existance, it really makes little sense
to depart from the established standards and so Oracle is back to producing
drivers (as opposed to competing, non-standard libraries).
Anyway, it looks like oo4o is still alive and kicking, although I would
certainly not start a new project with it.
> no need for anything other than ODBC, at least in Excel. I've got
> OO4OLE installed as well, but it's an old thing.
Well it still comes with the 9iR2 client and it looks recently updated.
Niall Litchfield
> Just had a look at the setup in the office and it's
> Excel only with ODBC. Then it's got the Excel database query pack
> installed. This activates the "Get External Data" option in
> Data menu. And the "Run Database Query" option. There appears to be
> no need for anything other than ODBC, at least in Excel. I've got
> OO4OLE installed as well, but it's an old thing.
> I'd expect Word to also be able to work with only ODBC but as you say,
> it's better to use OLEDB. At least it simplifies the VBA code.
OLEDB is the successor to ODBC. OO4O is an Oracle proprietory technology.
Its the fact that OLEDB is the later, faster (and still being updated)
technology which is why I would generally use it. Word will use ODBC, the
only thing that would require changing in my code would be the connection
string. Connection string formats can be found at www.connectionstrings.com
Noons
> Its the fact that OLEDB is the later, faster (and still being updated)
> technology which is why I would generally use it. Word will use ODBC, the
> only thing that would require changing in my code would be the connection
> string. Connection string formats can be found at www.connectionstrings.com
>
Thanks, chief.
I just reviewed my Word2K VBA book. Apparently, OLEDB
can actually use an ODBC driver, besides the specific OLEDB
ones provided by Oracle or whoever. Have you tried this
at all?
Interesting stuff. One wonders: why use specific report
writer tools when the best possible report writer is already
operational in one's PC and ready to go to the nearest db?
;)
--
Cheers
Nuno Souto
wizo...@yahoo.com.au.nospam
Niall Litchfield
news:Xns9358C7B7D...@210.49.20.254...
> Following up on Niall Litchfield, 09 Apr 2003:
>
> > Its the fact that OLEDB is the later, faster (and still being updated)
> > technology which is why I would generally use it. Word will use ODBC,
the
> > only thing that would require changing in my code would be the
connection
> > string. Connection string formats can be found at
www.connectionstrings.com
> >
>
> Thanks, chief.
> I just reviewed my Word2K VBA book. Apparently, OLEDB
> can actually use an ODBC driver, besides the specific OLEDB
> ones provided by Oracle or whoever. Have you tried this
> at all?
In a word No.
> Interesting stuff. One wonders: why use specific report
> writer tools when the best possible report writer is already
> operational in one's PC and ready to go to the nearest db?
> ;)
I guess because report writer tools mean that the report writer doesn't have
to do any programming. (yeah right). My impression is actually that many
organisations have the Office suite installed on every PC but never think to
automate it to streamline business processes. It might be that the task is
tried and found wanting, but my impression is it is found difficult and not
tried.
Telemachus
Noons
> I guess because report writer tools mean that the report writer doesn't
> have to do any programming. (yeah right).
exactly! Haven't yet seen one where that was the case...
> My impression is actually that
> many organisations have the Office suite installed on every PC but never
> think to automate it to streamline business processes. It might be that
> the task is tried and found wanting, but my impression is it is found
> difficult and not tried.
>
It's actually amazing how few sites have tried to use
Word or Excel as tools for report development.
I do visit many in my current employment and have asked
precisely this. Only seen one so far where it had actually
been considered and tried and was widely used.
Everywhere else I find people either don't know
it's possible, or think it "only works with Access"!
Oh well, I'll just keep using it myself... ;)
Phil Britton
> Following up on Niall Litchfield, 09 Apr 2003:
>
> > I guess because report writer tools mean that the report writer doesn't
> > have to do any programming. (yeah right).
>
> exactly! Haven't yet seen one where that was the case...
>
>
> > My impression is actually that
> > many organisations have the Office suite installed on every PC but never
> > think to automate it to streamline business processes. It might be that
> > the task is tried and found wanting, but my impression is it is found
> > difficult and not tried.
> >
>
> It's actually amazing how few sites have tried to use
> Word or Excel as tools for report development.
> I do visit many in my current employment and have asked
> precisely this. Only seen one so far where it had actually
> been considered and tried and was widely used.
>
North German Television and Radio (NDR) in Hamburg
use reports generated using Word and Oracle Reports as part of their
contracts management system. (I know that because I worked on
developing the systen as an Oracle consultant a few years ago)
So you're not the only one
<snip>
cheers
Phil