1

I am a beginner to excel 2007. I am trying to make a connection to the oracle database 10G and extract data from it. I am getting "Runtime error :'-2147467259(80004005)': Automation error unspecified error" . This code is working for my senior.Please Help!!

 Sub build_database_connection()

Dim cn As ADODB.Connection
Set cn= New ADODB.Connection
servername = example.com
UserName = example
pass = example

cn_cdcp.Open "Provider=MSDAORA.1;Data Source=" & servername & ";User ID=" & UserName & "Password=" & pass & ";"

End Sub
Community
  • 1
  • 1
Amritha Hariharan
  • 11
  • 4

1 Answers1

0

At first glance there are a few issues with you code above and I'm not sure if its just a copying of the problem but here I go anyway.

Sub build_database_connection()

    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    servername = "example.com" '<~~ the ".." defines a string
    UserName = "example" '<~~ the ".." defines a string
    pass = "example" '<~~ the ".." defines a string

    'cn_cdcp doesn't exist use cn instead
    'correct connection string should be 
    '"Provider=msdaora;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;"
    '<~~ you missed a ;
    cn.Open "Provider=MSDAORA.1;Data Source=" & servername & _
        ";User ID=" & UserName & ";Password=" & pass & ";"

End Sub

In saying the above I think your issue is in the connection string. See Connection Strings for more.

Yours should be Provider=msdaora;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;

glh
  • 4,900
  • 3
  • 23
  • 40