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,<blockquote>
Note: Same code works for connecting Teradata but failed when i use Oracle</blockquote>
'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<blockquote>
Also i tried creating new command object as cmdSQLData1 but still same error</blockquote>
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 knowAnswer1:
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.
Command objects is a <em>good</em> practice when you're re-executing the same query with different parameter values, but that's not the case here.
You do not need to use command text at all for those types of queries what you could do is :-
` Dim con As New ADODB.Connection Dim rs As New ADODB.Recordset
con.Open "DSN=Oracle", "User", "Password" rs.Open "select * from table_a", con ' Read all results rs.Close rs.Open "select * from table_b", con ' Read all results rs.Close con.Close
You only need to use "Command" if you plan to use a store procedure or a query with bound parameters.