?
Solved

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

Posted on 2004-08-25
3
Medium Priority
?
305 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
[X]
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
3 Comments
 
LVL 29

Accepted Solution

by:
leonstryker earned 1500 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

764 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