asked on

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.

SOLUTION

Wayne Taylor (webtubbs)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bond7

ASKER

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

ASKER CERTIFIED SOLUTION

Arthur_Wood

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bond7

ASKER

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

bond7

ASKER

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 .

bond7

ASKER

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

Arthur_Wood

do you have the Oracle clinet software installed on your PC? It is a free download from Oracle.

AW

Arthur_Wood

that should read CLIENT

bond7

ASKER

Hello,

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

Best
bond7