Solved

Select count(8) from tablename works in MS-Access, but not in Oracle.

Posted on 2004-08-25
3
294 Views
Last Modified: 2013-12-25
I have written an app in VB6 that needs to do a select count(*) or max(refid) from a table in Oracle. It works fine in MS-Access and in SQL-Plus Oracle tool, but VB comes back with Run Time error 3704 , "Operation is not allowed when the object is closed".

Code being used is
Private Function Get_New_ID() As Long
    'Generate a new internal REFID
    Dim lrs2 As New ADODB.Recordset
    lrs2.Open "Select max(refid) from tbl_toaster", gsConnect_String, adOpenStatic, adLockOptimistic
    If Not lrs2.EOF Then
        Get_New_ID = lrs2!REFID + 1
    Else
        Get_New_ID = 1
    End If
    lrs2.Close
End Function

gsconnect_string="DSN=UKDev;UID=fruit;PWD=orange;"

This select works fine in Oracle Sql-Plus!
Select max(refid) from tbl_toaster

It is urgent as I am on a client site and need to get this finished!!!!!!   Thanks.....
0
Comment
Question by:nolij
3 Comments
 
LVL 29

Accepted Solution

by:
leonstryker earned 500 total points
ID: 11897185
Which line are you getting the error on?  Try switching to a client side cursor.

Leon
0
 
LVL 39

Expert Comment

by:appari
ID: 11899537

try changing your

lrs2.Open "Select max(refid) from tbl_toaster", gsConnect_String, adOpenStatic, adLockOptimistic

to

lrs2.Open "Select max(refid) REFID  from tbl_toaster", gsConnect_String, adOpenStatic, adLockOptimistic
0
 
LVL 1

Expert Comment

by:Arnee_Senthil
ID: 11900577
Are you accessing another ORACLE user's table,

If so try this...

lrs2.Open "Select max(refid) from <USER_NAME.>tbl_toaster", gsConnect_String, adOpenStatic, adLockOptimistic

Include the <USER_NAME.> before the table name.

For example if you are using "SHOPPER" user's tables from FRUIT user then your qry must be like...

lrs2.Open "Select max(refid) from SHOPPER.tbl_toaster", gsConnect_String, adOpenStatic, adLockOptimistic

OR CHECK THE PRIVELEGES GIVEN TO FRUIT user...

Arne
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

713 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