Solved

create visual basic code on excel 2010

Posted on 2010-11-14
9
710 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:bond7
  • 5
  • 3
9 Comments
 
LVL 47

Assisted Solution

by:Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs) earned 100 total points
ID: 34130426
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
 

Author Comment

by:bond7
ID: 34130942
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
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 150 total points
ID: 34131273
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
 

Author Comment

by:bond7
ID: 34133162
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:bond7
ID: 34133317
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
 

Author Comment

by:bond7
ID: 34133678
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 34135461
do you have the Oracle clinet software installed on your PC?  It is a free download from Oracle.

AW
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 34135462
that should read CLIENT
0
 

Author Comment

by:bond7
ID: 34135954
Hello,

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

Best
bond7
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now