Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win



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.

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.


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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
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…
Suggested Courses

596 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