MS/Oracle ODBC only allowing 45 updates!

From: Neil Robinson <vtr_at_firestorm996.freeserve.co.uk>
Date: Mon, 10 May 1999 16:08:03 +0100
Message-ID: <7h6sfe$p4d$4@news4.svr.pol.co.uk>

I am using the following code to allocate a new order number as users request them...
(I'm not using basic SQL update commands as I had a previous problem with users being assigned the same order no when working over a slow ISDN line)

---

dim ws as workspace, dbs as database, rst as recordset dim NewWksOrder as long

Set ws = DBEngine.Workspaces(0)
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("ORDERTABLE")

ws.BeginTrans
With rst

.MoveFirst
.Edit

        !ORDER = !ORDER + 1


        .Update


        ws.CommitTrans
        NewWksOrder = !ORDER

End With

Set rst = Nothing
Set dbs = Nothing
set ws = nothing

---

I am using Access as a front-end to ODBC-linked Oracle 7.3 tables. I'm unable to alter the existing configuration of tables as several other systems also link to it, so using a sequence is not an option. The issue that I'm having is that, using the Microsoft ODBC driver I can run this procedure no more than 45 times before I get an error stating 'number of cursors exceeded'. I then have to close Access and re-open before I can continue. I am concerned that if a user does more than 45 new orders during one session they will also get this error. As far as I can see I'm not doing anything strange in my code that could cause the error. I can use the Oracle driver which allows a few more updates before giving me a similar error, but it doesn't solve the problem

Has anyone come across this problem before?

--
Neil Robinson
VTR1000 Firestorm Received on Mon May 10 1999 - 10:08:03 CDT