Accessing Data from an oracle database on a different server using

[Rohan Pradhan]

Hi ,
I want to extract data from an oracle database and display it in an Excel
worksheet . I am not a regular programmer and do not know how to go about
with it. Can someone please help me out. Thanks in advance

 

[NateBuckley]

First you will need to set up a reference to the various objects that you
will need to use to make the connection.

Go to Macro Editing Screen (Visual Basic Editor) from Excel, alternativly
press Alt+F11 then go to Tools - References and Check "Microsoft ActiveX data
Object 2.8 Library" and click ok. (you may have a different version but one
of the higher numbers should do)

Now you can connect to an Oracle database with the following code
Sub ConnectToDB()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim dbPath as String

dbPath = "C:\db\db.db" 'Unsure if thats the extension for oracle
Set con = New ADODB.Connection
con.Provider = "msdaora"
con.Open "Data source=" & dbPath & ";User ID=test;Password=pwd;"

strSQL = "SELECT * FROM tblEmployees"

Set rs = New ADODB.Recordset
rst.Open strSQL, con, adOpenDynamic, adLockReadOnly

While Not rs.eof
MsgBox rs.fields!EmployeeName
Wend

rs.close
con.close

Set rs = Nothing
Set con = Nothing
End Sub

You'll need to learn some SQL statements to extract more information from
your database, but it's really easy and you'll be doing complex db
interactions in no time.

I haven't tested this as I don't have access to Oracle here, but I'm quite
sure thats hwo I did it at home. (at work)

 

[Tim Williams]

Just to add to Nate's post - don't forget you'll need the Oracle Client
package installed on any machine you want to run this on.

This:

dbPath = "C:\db\db.db"

will instead look something like:

dbPath = "myDBName"

...since you're accessing a DB on a remote server. "myDBName" will be the
name defined in your [local] tns_names.ora file (basically a "lookup" file
for remote database instances which maps "names" to server details). Ask
your DBA if you need assistance with this.

Tim