[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

urgent - connecting to oracle db using vba

Posted on 2005-03-24
16
Medium Priority
?
1,124 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
  • 7
  • 4
  • 3
14 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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 56

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 56

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month11 days, 1 hour left to enroll

612 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