vba ODBC gives Unspecified error when oracle table

Question

I have vba code to fetch Oracle tables data in to a recordset via ODBC. But it shows some unspecified error if the oracle table is having timestamp field in it.

I have 100+ table i dont know which table will have timestamp. I am running below query in loop to retrieve data.

Query = "Select End_Time from MyTable" 'this table has End_Time timestamp(6) field
CmdSQLData.CommandText = Query
CmdSQLData.CommandType = adcmdText  
CmdSQLData.Timeout=0 set rs = CmdSQLData.Execute() 'This line shows unspecified error then the table is having timestamp field 
'Then code for store data here...

RunTime Error '-2147467259 (80004005)'; Unspecified error

Oracle Table structure is :

create table MyTable    (
Date_id Integer,
Date_Today Date,
End_Time Timestamp(6)
)

Please do not suggest some thing like,

select to_char(timestamp_field) from my_table

If i do above query, then the problem is not coming. I need permanent code to handle Timestamps in Recordsets because i may not know whether the table is having timestamp field or not as i have 100+ tables

Answer 1

The following code works fine for me :-

Dim con As New ADODB.Connection
Dim cmd As New ADODB.command
Dim rs As New ADODB.Recordset

con.Open "DSN=Oracle", "test", "test"
con.Execute "drop table tstest "
con.Execute "create table tstest ( a int, b Timestamp(6))"
con.Execute "insert into tstest values ( 1, current_timestamp ) "

q = "select * from tstest"
cmd.ActiveConnection = con
cmd.CommandText = q
cmd.CommandType = adCmdText
Set rs = cmd.Execute

Debug.Print rs.Fields(1)

con.Close

This is using ADO 2.8 with Oracle driver version 11.1.0.7

* NOTE : The Oracle driver comes from Oracle - you need to get it to work the :-

Instant Client Package - Basic
Instant Client Package - ODBC

You need both packages to get the driver to work. You then need to use an Instant Client connection in your ODBC data source for example :-

192.168.0.25:1521/orcl

IP:Port/Name

Answer 2

You may be running into something like the following: https://forums.oracle.com/forums/thread.jspa?messageID=4210331

Try upgrading your ODBC client.