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

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.....
nolijAsked:
Who is Participating?
 
leonstrykerConnect With a Mentor Commented:
Which line are you getting the error on?  Try switching to a client side cursor.

Leon
0
 
appariCommented:

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
 
Arnee_SenthilCommented:
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
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.

All Courses

From novice to tech pro — start learning today.