Improve company productivity with a Business Account.Sign Up



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
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 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.


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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

608 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