Solved

create visual basic code on excel 2010

Posted on 2010-11-14
9
713 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…

820 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