PDA

View Full Version : Connect to oracle from excel



sarat
07-04-2012, 06:06 AM
Hi all,
I am very new to this field. I want to connect to oracle through VBA and retrieve data from table. I referred so many link but still not come to a final conclusion.
I am really frustrated. Can anyone provide the code.
I have select ADO object from tool-->Reference-->microsoft activex ADO 2.1 Object Library using odbc connection.

<-------Information---->
Data source name--jai
Username-scott
password----tiger
server---oracl

Bob Phillips
07-11-2012, 03:04 AM
Lookup connection strings Oracle on Google to get started.

online
07-19-2012, 02:48 AM
Hi Sarat,

Here is code just copy and paste in new module

Sub connectDB()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strHost, strDatabase, pwd, strUser, strPassword As String
Set conn = New ADODB.Connection


strHost = "localhost"
strDatabase = "ORCL"

strUser = "HR"
strPassword = "password"

conn.ConnectionString = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=" & strHost & ")(PORT=1521))" & _
"(CONNECT_DATA=(SERVICE_NAME=" & strDatabase & "))); uid=" & strUser & " ;pwd=" & strPassword & ";"

conn.CommandTimeout = 100000

conn.Open

Set rs = New ADODB.Recordset

rs.Open "Employees", conn, adOpenKeyset, adLockBatchOptimistic

ActiveCell.CopyFromRecordset rs

conn.Close

Set rs = Nothing
Set conn = Nothing

End Sub