Solved

create visual basic code on excel 2010

Posted on 2010-11-14
9
712 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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 

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
 

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

ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
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…

803 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