asked on

urgent - connecting to oracle db using vba

Hello
I am doing excel macro programming and I want to connect to oracle database using vba, I am easily able to connect to the access db but im not able to connect to the oracle db, its giving me runtime error with my below given code.

what are the things that i need to connect to the oracle db?
what reference should i give?
where will i find the datasource name?

could anyone please help......thanks

----------------
Dim strConn As String
Dim cn As New ADODB.Connection

Set cn = New ADODB.Connection
Set rsOra = New ADODB.Recordset

strConn = "Provider=MSDAORA.1;DataSource=?????;UserID=a_test;Password=t2004;Persist "
Security = False
cn.ConnectionString = strConn
cn.Open

End Sub

leonstryker

There is a tsnames.ora file on you computer somewhere (or at least there should be if you have a client installed). This file acts as an ini file for Oracle. In this file you should have a a name (like a DSN) for the Oracle server you are connecting to. That is the name which goes into the DataSource parameter.

Leon

Ryan Chong

Additional references that may useful:
http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForOracleFromMicrosoft
http://www.able-consulting.com/MDAC/ADO/Connection/ODBC_DSNLess.htm#ODBCDriverForOracleFromMicrosoft

sunshine737

ASKER

well i have tried doing them before, but i was always getting this runtime error '-2147217843 (80040e4d): Automations error '

Dim sqlStr As String ' sql string
Dim oConn As ADODB.Connection
Set oConn = New ADODB.Connection
Dim oRec As ADODB.Recordset
Set oRec = New ADODB.Recordset

oConn.Open "Driver={Microsoft ODBC for Oracle};" & _
"Server=A.world;" & _
"Uid=A_TEST;" & _
"Pwd=xxxx"

leonstryker

Did you locate the tsnames.ora file?

sunshine737

ASKER

yes i located the tnsnames.ora

and it looks something like this, there is no DSN name

###############################################################################
A_P.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =(COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = h.com)(Port = ****)))
(CONNECT_DATA = (SID = ****)))
########################################################################

sunshine737

ASKER

using java i connected to the same database, and i didnt face any problem, i dont know why vb is giving me problem

("jdbc:oracle:thin:@0:1:a","a_t", "-----");

sunshine737

ASKER

its giving me the same error
runtime error '-2147467259 (80004005): Automations error '

leonstryker

Try this:

oConn.Open "Provider=OraOLEDB.Oracle;" & _
"Data Source=A_P.world;" & _
"User Id=A_TEST;" & _
"Password=xxxxx"

sunshine737

ASKER

>>>oConn.Open "Provider=OraOLEDB.Oracle;" & _
"Data Source=A_P.world;" & _
"User Id=A_TEST;" & _
"Password=xxxxx"

no use im getting the same runtime error - '-2147467259 (80004005)'

what could be the reason?????????????????????im banging my heas since many days

does somebody have any idea

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sunshine737

ASKER

tried but still not working.......shld i reboot my system????

sunshine737

ASKER

does my connection depend on driver

i mean i have seen some old code and they tried with oraDatabase

and when im trying it my vb is not recognising, shoud i refrence something to get this driver

totally confused

leonstryker

>tried but still not working.......

Were you able to create DSN as ryancys suggested?

Ryan Chong

What's your version of your Oracle Server, and have you tried success connect to it via tools like SQL Plus? Have you install the Oracle Client on target machine, that running the codes?