CODEWITHSUNDEEP
oracle-databasevbaexcel
ZalesaK
ZalesaK

Reputation: 11

Unspecified error while executing sql query vba on Oracle database

I want to import data from Oracle database to Excel using VBA. I've tried several options I found in here or in the official manual but none of them seem to work for me - I always get the same unspecified runtime error on line with 

rs.Open strSQL1, con

or

Set rs = con.Execute(strSQL1)

depending on which one of these two methods I use obviously. Here is the whole code:

Sub data_distribution()

'Setting up the database connection
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL1, strInput, strCon As String

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset

strCon = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=XXX)(PORT=XXX))" & _
"(CONNECT_DATA=(SERVICE_NAME=XXX))); uid=XXX; pwd=XXX;"

'---  Open the above connection string.
con.ConnectionString = strCon
con.Open
'con.Open strCon

'---  Now connection is open and you can use queries to execute them.
'---  It will be open till you close the connection

'Definition of parameter
strInput = InputBox("Insert car_id")

strSQL1 = "select * from car where car_id = " & strInput & ""

'Executing the query
'rs.activeconnection = con
'rs.Open strSQL1, con

Set rs = con.Execute(strSQL1)
rs.Open strSQL1, con

Worksheets("Data").Range("A2").CopyFromRecordset rs

'Closing connection
rs.Close
con.Close
Set con = Nothing
Set rs = Nothing

I was thinking it might be an issue of connecting to the database but if I import/query data manual, it works just fine. For example using this manual

http://blog.mclaughlinsoftware.com/microsoft-excel/how-to-query-oracle-from-excel-2007/

I had to download and configure ODAC for Windows from Oracle site to make it work. I use tsnames.ora to set the path. Wasn't sure if I configured it right but it works so I guess there isn't a problem with the connection in vba either, or is it? (The variables are, of course, set to real values, not "XXX")

The query itself is correct and returns valid results from the database. Libraries necessary to use ADOBD are linked as well. (Microsoft ActiveX Data Objects 2.0 Library)

There is an existing question with the same error but it's unresolved:

Unspecified run time error while executing vba script

I'm guessing it's station/interface specific (I use Excel 2010, SQL developer).

Upvotes: 0

Views: 2258

Answers (1)

ZalesaK
ZalesaK

Reputation: 11

Even though it's been some time I asked the question, here, it's still unresolved so I will answer myself.

The problem was in the car table where one of the attributes was type CLOB (CHARACTER LARGE OBJECT) with lenght over 2000 characters. Excel was unable to cope with that type and the query caused the unspecified error. If I listed all attributes but this one, it all went well.

Upvotes: 1

Related Questions