Solved: Connect to Oracle

Hi,

i try to connect to oracle and also i add the references "Oracle In Proc Server 4.0 Type Library" but still i'm getting error - "The specified module could not be found"

[VBA]
Sub ConnectToOracle()
Dim objSession As Object
Dim objDatabase As Object

' Create a reference to the OO40 dll
Set objSession = CreateObject("OracleInProcServer.XOraSession")

' Create a reference to my database
Set objDatabase = objSession.opendatabase("Oracle", "scott/tiger", 0&)
End Sub
[/VBA]

thanks..

does anyone know how to connect to oracle from excel? :help
thanks..

After adding a reference to the latest MDAC, use this.

Code:

---

    Set gconConnection = New Connection
    With gconConnection
        .ConnectionString = "Provider=MSDAORA;" & _
                            "Data Source=Oracle database name;" & _
                            "User ID=scott;Password=tiger;"
        .Open
    End With

---

Thanks Little PD for the reply..
i'll try that code to connect to oracle..


thanks,

hi all,
i wanna asking for connection with oracle and vba using this source but i got error object required. can anybody help me?
[VBA]Dim strConnection, conn, rs, strSQL
strConnection = "Provider=OraOleDb.Oracle.1;Password=123456;Persist Security Info=True;User ID=viola-0.5.0;Data Source=(DESCRIPTION =(ADDRESS=(PROTOCOL=TCP)(HOST=v-repo-01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=XE))"


Set conn = Server.CreateObject("ADODB.Connection")
conn.open strConnection

Set rs = Server.CreateObject("ADODB.recordset")
strSQL = "SELECT * FROM AIRLINE"
rs.open strSQL, conn, 3, 3

rs.MoveFirst
While Not rs.EOF
RSWApp.WriteToLog rs("ID"), rs("CODE"), rs("NAME")
rs.MoveNext
Wend

rs.Close
Set rs = Nothing

conn.Close
Set conn = Nothing
[/VBA]
:banghead: please help me

maybe it can help you with this.. i got the idea from LittlePD.. :thumb
pls add reference to Microsoft ActiveX Data Object..

Sample:

[VBA]
Sub test()
Dim conn, rs
Dim strSQL As String

Set conn = CreateObject("ADODB.Connection")
With conn
.ConnectionString = "Provider=MSDAORA;" & _
"Data Source=ORCL10G;" & _
"User ID=System;Password=oracle;"
.Open
End With

Set rs = CreateObject("ADODB.Recordset")
strSQL = "select * from scott.emp"
rs.Open strSQL, conn, 3, 3

Range("A1") = rs("ENAME")
Range("B1") = rs("JOB")

rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
End Sub
[/VBA]

:beerchug:
thank you very much for your response. thats source can make my vba and oracle connected :thumb

kindly mark it solved., if your problem has been solve..

:hi:

opss i got eror message about ORA-12516: TNS:listener could not find available handler with matching. what that mean?
it happen when i run it until 3 times (click run button)

Did you change the data source in ConnectionString?

Quote:

---

Originally Posted by gnod

Did you change the data source in ConnectionString?

---

yes i do. because if i'am not change it i've got the error ORA-12154:TNS:Could not resolved the conect identifier specified.

so i change that Provider=MSDAORA with server=v-repo-01
where is the name of listener host.

hi,

when i try the code here in my office, i also encounter ORA-12154: TNS: Could not resolve service name..

i already change the TNSnames.ora in my PC

pls :help

hi,


does anyone know how to slve my prblem?

pls :help


thanks,

hello,
Do you ever check local window in your vba window you can display that with click tab view and click local window.
[vba].ConnectionString = "where is database you save like server=v-repo-01" & _
"Data Source=name of database;" & _
"User ID=System;Password=oracle;"
.Open [/vba] and then you can see this in local window in your connectionstring
[vba]
ConnectionString : "Provider=MSDASQL.1;Password=123456;User ID=viola-0.5.0;Data Source=viola-0.5.0;Extended Properties="DSN=viola-0.5.0;UID=viola-0.5.0;PWD=123456;SERVER=v-repo-01;"
[/vba]

thanks for your reply..
it was already solved.. i input a worng datasource which is currently setup in our office..



thanks,

did you use that littlePD source? and where you've got the mistake? can you describe me?

i input a wrong datasource in the ConnectionString

not in your provider that you input? what do you thing with my connectionstring with change in provider?because sometime there is an error i mentioned

not in your provider that you input? what do you thing with my connectionstring with change in provider?because sometime there is an error i mentioned