Solved

create visual basic code on excel 2010

Posted on 2010-11-14
9
717 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
[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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

724 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