Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Data from an oracle database on a different server using
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Data from an oracle database on a different server using
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) "Rohan Pradhan" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Data from an oracle database on a different server using
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 "NateBuckley" wrote in message ... 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) "Rohan Pradhan" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP: VBA Code to extract data from an SQL Server database and put it into Access Database | Excel Programming | |||
Retrieve data from Oracle Database | Excel Programming | |||
Accessing oracle database through excel cell | Excel Discussion (Misc queries) | |||
How to get data from Oracle Database to Excel? | New Users to Excel | |||
Forms accessing SQL Server data. | Excel Programming |