create visual basic code on excel 2010

Hello,

I am new to VBA.I need to create a connection to database{oracle} and excute query.
I have opened the vba editor in excel 2010.
I tried typing some code in it

Sub Macro1()
 
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
 
cnn.ConnectionString = "Driver={Microsoft ODBC for Oracle}; " & _
"Server=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=yourHost)(PORT=yourPort)))(CONNECT_DATA=(SERVICE_NAME=yourServicename)(SERVER=DEDICATED)));User Id=yourUser;Password=yourPwd;"
 
cnn.Open
Set rs = cnn.Execute("select count(*) cnt from my_table")
 
Debug.Print rs("cnt").Value
 
cnn.Close
 
End Sub

It fails at Dim cnn As New ADODB.Connection , saying user defiend type not found.

Please suggest how to proceed.
bond7Asked:
Who is Participating?
 
Arthur_WoodConnect With a Mentor Commented:
Since you did not have the Reference, mentioned by webtubbs, the compiler assumed that the line

Dim cnn As New ADODB.Connection

was to create a new instance of a user defined class, which was not defined.  Hence the error message.  When you add the Reference to the "Microsoft ActiveX Data Objects" (ActiveX Data Objects) the error message will go away.
0
 
Wayne Taylor (webtubbs)Connect With a Mentor Commented:
You need to add a reference to "Microsoft ActiveX Data Objects". Do this by going to Tools > References and scroll down to the mentioned library. You may have several versions of this library, so select the highest one available.

Wayne
0
 
bond7Author Commented:
Hello,

Could you please elaborate on the steps for  "It fails at Dim cnn As New ADODB.Connection , saying user defiend type not found."



Best Regards
bond7
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
bond7Author Commented:
Hi,

I added the reference as mentioned, now I am getting another error -
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Best Regards
bond7
0
 
bond7Author Commented:
Hello,

I found another VBA code which listed down the installed drivers and I found the Oracle in OraDb10g_home1.
I have changed the driver to Oracle in OraDb10g_home1.
Now the error is ORA-12560 .
0
 
bond7Author Commented:
hello,

I check the Microsoft ODBC for Oracle message ->  this driver is not found.
Oracle in OraDb10g_home1 - message ->  Specified driver could not be loaded due to system error 998.

i also tried creating new DSN, then it gives same error as above.

I am thinking it is due to drivers not found or drivers not accessible.

Please suggest
0
 
Arthur_WoodCommented:
do you have the Oracle clinet software installed on your PC?  It is a free download from Oracle.

AW
0
 
Arthur_WoodCommented:
that should read CLIENT
0
 
bond7Author Commented:
Hello,

I have Oracle 9i , Oracle database 10g and iAs oracle 10g on laptop.

Best
bond7
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.