?
Solved

urgent - connecting to oracle db using vba

Posted on 2005-03-24
16
Medium Priority
?
1,111 Views
Last Modified: 2013-12-25
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

0
Comment
Question by:vihar123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
16 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 13624207
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
0
 

Author Comment

by:vihar123
ID: 13650350
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"

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 29

Expert Comment

by:leonstryker
ID: 13652445
Did you locate the tsnames.ora file?
0
 

Author Comment

by:vihar123
ID: 13659891
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 = ****)))
########################################################################
0
 

Author Comment

by:vihar123
ID: 13659909
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", "-----");
           
0
 

Author Comment

by:vihar123
ID: 13660837
its giving me the same error
 runtime error '-2147467259 (80004005): Automations error '
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 13661969
Try this:

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

Author Comment

by:vihar123
ID: 13725100
>>>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
0
 
LVL 53

Accepted Solution

by:
Ryan Chong earned 2000 total points
ID: 13725512
try use:

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

where before that, you need to create an entry at ODBC entry. try this:

1. goto Start > Run, type in odbcad32
2. ODBC Data Source Administrator window apprears, goto System DSN tab
3. Click the Add button, and then create a Oracle ODBC Driver entry there.
0
 

Author Comment

by:vihar123
ID: 13725879
tried but still not working.......shld i reboot my system????
0
 

Author Comment

by:vihar123
ID: 13725940
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
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 13726678
>tried but still not working.......

Were you able to create DSN as ryancys suggested?
0
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 13727998
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?
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month9 days, 21 hours left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question