Solved

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

Posted on 2004-08-25
3
260 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now