Oracle database query throws error on second run

Question

i have an VBA code where i am calling oracle to retrieve data twice using ODBC. First data retrieval is fine. But 2nd time it is saying ,

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

My Code is as follows,

Note: Same code works for connecting Teradata but failed when i use Oracle

'First Data retrieval
Query1 = "Select TableName from all_tables"
CmdSQLData.CommandText = Query1
CmdSQLData.CommandType = adcmdText 
CmdSQLData.Timeout=0
set rs = CmdSQLData.Execute()
'Then code to store data ...
'This part gives proper result ...
rs.close()

'Second Data retrieval
Query2 = "Select * from db.Event"
CmdSQLData.CommandText = Query2
CmdSQLData.CommandType = adcmdText 
CmdSQLData.Timeout=0
set rs = CmdSQLData.Execute() 'This line Gives Error - RunTime Error '-2147467259 (80004005)'; Unspecified error

Also i tried creating new command object as cmdSQLData1 but still same error

May i know why the error is coming for second query ? There is no problem with query as i have tested in oracle directory. Please let me know

Answer 1

You won't see this documented much of anywhere, but reusing Command objects with different comamndText is actually a bad practice. You don't say what kind of connection you're using, but for example if it's ODBC, this will internally send a fake invalid SQL to Oracle to force a cleanup of some kind. So instead, throw away your Command object after use and create a new one.

Reusing Command objects is a good practice when you're re-executing the same query with different parameter values, but that's not the case here.