• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

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.....
0
nolij
Asked:
nolij
1 Solution
 
leonstrykerCommented:
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now