Posted on 2006-06-09
Medium Priority
Last Modified: 2010-08-05
I have a Ms-Access 2002 SP3 client and Oracle DB 9i in Solaris Unix. In the code when I check the StillExecuting flag for the recordset bound to the query I get the above error (63535 or 3146). The thing is that this happens occasinally and there are other times where the query will complete its execution cycle in the Oracle server perfectly! I use ODBC direct to create and send the queries. Any suggestions on what might be the problem. The ODBC driver I use is the Oracle ODBC V.

If Not rstODBC.StillExecuting Then

Any suggestions? Is something wrong with the ODBC driver / Network connection?
Question by:skapodis
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 13

Expert Comment

by:John Mc Hale
ID: 16868850
When you open the recordset, do you need to specify whether to use the Client side or Server side Cursor. I dunno, but maybe this might have something to do with it??

Expert Comment

ID: 16869447
I think StillExecuting does not work well with other than microsoft databases.. check the documentation

Author Comment

ID: 16869820
This is how I initiate the query to the Oracle Server through ODBC:

Set dbs = CurrentDb()
Set odbcWorkspace = CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC)
Workspaces.Append odbcWorkspace
Set OracleConnection = odbcWorkspace.OpenConnection("OracleDB", dbDriverNoPrompt, False, "ODBC;DSN=OracleDB;UID=BGW;PWD=THULE;")
Set qdfSearch = OracleConnection.CreateQueryDef("")          
qdfSearch.ODBCTimeout = 0
qdfSearch.SQL = SqlString
Set rstODBC = qdfSearch.OpenRecordset(dbOpenDynaset, dbRunAsync) <--- I open a Dynaset recordset type which is similar to an ODBC keyset cursor. If you do not specify the dbOpenDynaset the recordset is opened as a Forward-only type which corresponds to an ODBC forward-only cursor.

Also the above objects (queryDefs, Connections etc) are declared as Global in an Ms-Access module object so I can reference them anywhere in the code. Of course I take care not to reinitialise them once the query is executing in Oracle Server.

I did not find any relevant info on a StillExecuting problem apart from the fact that it had a problem with the True/False value which is fixed in VB.6 libraries.

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.


Author Comment

ID: 16900254
I have updated the code to a more simple version with a modification which seems to have solved the problem (need to do more testing though):

Set OracleConnection = odbcWorkspace.OpenConnection("OracleDB", dbDriverNoPrompt, False, "ODBC;DSN=OracleDB;UID=BGW;PWD=THULE;")
OracleConnection.QueryTimeout = 0
Set rstODBC = OracleConnection.OpenRecordset(GlobalStringSql, dbOpenDynaset, dbRunAsync, dbReadOnly)
rstODBC.Connection.QueryTimeout = 0

Pay attention to the OpenRecordset command. I have added the dbReadOnly flag for the dbOpenDynaset recordset type. Since the error seemed to occur only for queries that had to search through vast ammount of data (searches for more that 10million records) I realised that the dbOpenDynaset recordset may by default make locks on the records matching the SQL criteria possibly returning an error due to these locks which to be honest I do not know if they remain active as long as the recordset is alive. Adding the dbReadOnly does not allow any locks to be placed possibly solving the problem. Can anyone comment/confirm on that?  

Author Comment

ID: 16910355
Nope still get the error 63535! When I get the error I can see that the session in Oracle is not gone. Howeverthe search does not make any more reads meaning that it has stopped. Are ther any hidden timeouts in Access for connection, recorset objects? Do I need to retreive the matched data regularly from Oracle? Does Oracle stops the query execution due to certain profile settings?

Author Comment

ID: 16912329
Just it case it help someone figure out a solution... The error does not seem to appear if the main query is brake up by the Oracle SQL Optimiser into smaller queries running in parallel which of course speeds up the search time! If this is done the results once the query ends are fetched normally back to MS-Access. I will always get the error if the query is not optimized (possibly due to heavy use of the Oracle DB).

Author Comment

ID: 16933746
Finally after a trace in the ODBC data source I found out that the actual Oracle error is ORA-0155 which of course refers to the rollback segments. After increasing the rollback segment from 300MB to 2GB the error appears rarely.

Accepted Solution

CetusMOD earned 0 total points
ID: 17258198
PAQed with points refunded (500)

Community Support Moderator

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question