Unspecified run time error while executing vba script

Question

I am new to vba and I am using vba script to connect to database from excel and get the records. I have written the following script for that.I am getting a run time error '-2147467259(80004005)':Unspecified error. How to resolve this error. See the error screen shot.

 Sub Ora_Connection()
        Dim con As ADODB.Connection
        Dim rs As ADODB.Recordset
        Dim query As String

        Set con = New ADODB.Connection
        Set rs = New ADODB.Recordset
        '---  Replace below highlighted names with the corresponding values
        strCon = "Driver={Microsoft ODBC for Oracle}; " & _
        "CONNECTSTRING=(DESCRIPTION=" & _
        "(ADDRESS=(PROTOCOL=TCP)" & _
        "(HOST=host_name)(PORT=1521))" & _
        "(CONNECT_DATA=(SERVICE_NAME=service_name))); uid=id; pwd=pw;"
        '---  Open the above connection string.
        con.Open (strCon)
        '---  Now connection is open and you can use queries to execute them.
        '---  It will be open till you close the connection
        query = "select * from security.forms"
        Set rs = con.Execute(query)

        For i = 0 To rs.Fields.Count - 1
            Sheet1.Cells(1, i + 1).Value = rs.Fields(i).Value
        Next



        con.Close
        End Sub

Error screen shot: Error Message

What happens when you click debug? Which line is highlighted? — Gareth
– Gareth, Commented Jul 18, 2014 at 12:56
Have you tested your query select * from security.forms work on a DB client? — Gareth
– Gareth, Commented Jul 18, 2014 at 13:01
yes, When I execute this query in toad it is givng the records. — Sandeep Reddy K.
– Sandeep Reddy K., Commented Jul 18, 2014 at 13:03
Are you sure the values in the connection string are correct? Looking at it you've got SERVICE_NANE=service_name along with uid=id and pwd=pw — Gareth
– Gareth, Commented Jul 18, 2014 at 13:08

e_jalbert · Accepted Answer · 2014-07-18 13:56:26Z

0

Had you tried to use the open method of your RecordSet instance? Maybe it will give you another error that will be more helpful.

Dim connection As New ADODB.connection
Dim rst As New ADODB.Recordset
Dim query As String

connection.ConnectionString = CONNECTION_STRING
connection.Open

rst.Open query, connection, adOpenKeyset, adLockOptimistic

do while not rst.EOF
   rst.MoveNext
loop

connection.Close

answered Jul 18, 2014 at 13:56

e_jalbert

363 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SeanKw · Accepted Answer · 2016-01-14 15:31:21Z

0

I had the exact same problem and it was tough to find an answer since most posts on this issue are unanswered.

I solved it by using another Oracle driver. Instead of Microsoft ODBC for Oracle try using the Oracle in OraClient11g_home1 driver. Hope this helps

answered Jan 14, 2016 at 15:31

SeanKw

631 silver badge7 bronze badges

Comments

Karthick Gunasekaran · Accepted Answer · 2014-07-18 13:05:30Z

-1

Please add the reference 'Microsoft ActiveX Data Objects 2.8 Library'

enter image description here

answered Jul 18, 2014 at 13:05

Karthick Gunasekaran

2,7231 gold badge17 silver badges26 bronze badges

2 Comments

Gareth Over a year ago

Pretty sure that if ADO isn't referenced, excel will throw a User-Defined type not defined error not an unspecified error.

Sandeep Reddy K. Over a year ago

I have already added that ADO reference. Still Getting the same error.

Collectives™ on Stack Overflow

Unspecified run time error while executing vba script

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related