Re: Word to Oracle via VBA

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Tue, 8 Apr 2003 09:21:05 +0100
Message-ID: <3e928671$0$22004$ed9e5944@reading.news.pipex.net>

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

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" <wizofoz2k_at_yahoo.com.au> wrote in message
news:73e20c6c.0304071748.29e7b51a_at_posting.google.com...


> esmarandache_at_tucsonelectric.com (Ellie Smarandache) wrote in message

news:<c6fbfcfa.0304071016.6306f5d8_at_posting.google.com>...


> >

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

> wizofoz2k_at_yahoo.com.au.nospam

Received on Tue Apr 08 2003 - 03:21:05 CDT