PDA

View Full Version : Solved: Connect to Oracle



gnod
03-20-2008, 07:53 AM
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"


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


thanks..

gnod
03-20-2008, 07:57 PM
does anyone know how to connect to oracle from excel? :help
thanks..

LittlePD
03-21-2008, 03:57 PM
After adding a reference to the latest MDAC, use this.



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

gnod
04-12-2008, 07:44 PM
Thanks Little PD for the reply..
i'll try that code to connect to oracle..


thanks,

uthee_85
07-02-2008, 03:09 AM
hi all,
i wanna asking for connection with oracle and vba using this source but i got error object required. can anybody help me?
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

:banghead: please help me

gnod
07-07-2008, 08:58 AM
maybe it can help you with this.. i got the idea from LittlePD.. :thumb
pls add reference to Microsoft ActiveX Data Object..

Sample:


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

uthee_85
07-07-2008, 07:56 PM
:beerchug:
thank you very much for your response. thats source can make my vba and oracle connected :thumb

gnod
07-07-2008, 10:25 PM
kindly mark it solved., if your problem has been solve..

:hi:

uthee_85
07-07-2008, 11:48 PM
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)

gnod
07-08-2008, 12:42 AM
Did you change the data source in ConnectionString?

uthee_85
07-08-2008, 01:24 AM
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.

gnod
07-09-2008, 04:29 AM
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

gnod
07-10-2008, 04:35 AM
hi,


does anyone know how to slve my prblem?

pls :help


thanks,

uthee_85
07-15-2008, 08:13 PM
hello,
Do you ever check local window in your vba window you can display that with click tab view and click local window.
.ConnectionString = "where is database you save like server=v-repo-01" & _
"Data Source=name of database;" & _
"User ID=System;Password=oracle;"
.Open and then you can see this in local window in your connectionstring

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;"

gnod
07-16-2008, 08:34 AM
thanks for your reply..
it was already solved.. i input a worng datasource which is currently setup in our office..



thanks,

uthee_85
07-17-2008, 01:04 AM
did you use that littlePD source? and where you've got the mistake? can you describe me?

gnod
07-17-2008, 11:02 PM
i input a wrong datasource in the ConnectionString

uthee_85
07-18-2008, 12:55 AM
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

uthee_85
07-18-2008, 12:55 AM
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