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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.