View Full Version : 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"
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..
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
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
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
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)
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.
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,
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;"
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?
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.