PDA

View Full Version : oracle connectivity



sathishesb
07-28-2011, 03:09 AM
Can you please suggest me some way how to connect to a oracle database using excel and want to execute some sql query, for a sample


select name,marks from student where marks>30

i need to fetch the data in a table format.

Please guide me :help

Aflatoon
07-28-2011, 03:17 AM
Here is an example. You may need to use a different provider depending on which version of the Oracle client tools you have installed:


Sub GetOracleData()
' Sample demonstrating how to return a recordset from an Oracle db
' requires a reference to the Microsoft ActiveX Data Objects Library.

Dim cn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset, TR As Range
Dim varData, lngRecCount As Long, lngFieldCount As Long, avarData() As Variant
Dim i As Long, j As Long

Application.ScreenUpdating = False

Set TR = Range("A1")
Set cn = New ADODB.Connection
With cn
.Provider = "MSDAORA.Oracle"
' .Provider = "OraOLEDB.Oracle"
.ConnectionString = "Data Source=database;User ID=itsme;Password=secret"
.Open
End With
strQuery = "SELECT * FROM table_name WHERE some_field < 100"
Set rst = New ADODB.Recordset
rst.Open strQuery, cn, adOpenStatic, adLockReadOnly, adCmdText

With rst
lngFieldCount = .Fields.Count
' output the field names
For i = 1 To lngFieldCount
TR.Offset(0, i - 1) = .Fields(i - 1).Name
Next i
' use CopyFromRecordset method to output data on worksheet
TR.Offset(1, 0).CopyFromRecordset rst
.Close
End With

Set rst = Nothing
cn.Close
Set cn = Nothing
Application.ScreenUpdating = True
End Sub

sathishesb
07-28-2011, 03:42 AM
Thanks a lot for the code.
I am getting an error at
Dim cn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset, TR As Range

with a error message as,


user-defined type not defined

is there any change i need to do to the code, other than connection string.:dunno

Aflatoon
07-28-2011, 03:50 AM
If you were to read the comment line above the first declaration line, you would see what is required. ;)

sathishesb
07-28-2011, 04:19 AM
sorry for this question,
It throws an error such as,


TNS names couldn't be resolved

but i am 200% sure i am giving correct data source name, user and password.

do i need to change anything in this part

With cn
.Provider = "MSDAORA.Oracle"
'.Provider = "OraOLEDB.Oracle"
.ConnectionString = "Data Source=abcd;User ID=xxxx;Password=yyyyy"
.Open
End With

i am trying to connect to Oracle data base..

please help me

Aflatoon
07-28-2011, 04:28 AM
Does the data source work if you connect using MSquery? Are you sure your client tools are set up properly?

sathishesb
07-28-2011, 04:50 AM
ya both my client and Query works fine in other tool, but throws the same error in vba ..

Aflatoon
07-28-2011, 05:29 AM
It may be that you need a different provider. I would check a site like www.connectionstrings.com for your Oracle version.