PRB: Error When You Use adLockBatchOptimistic with OLE DB Provider

Article translations Article translations
Article ID: 261297 - View products that this article applies to.
This article was previously published under Q261297
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page


When you add records to an ADO recordset object and set the LockType value to adLockBatchOptimistic, you may receive the following error message:
Microsoft JET Database Engine error '80040e21'
Errors occurred
/a.asp, line xx
Provider (0x80040E54)
Number of rows with pending changes exceeded the limit.
/a.asp, line xx
Microsoft OLE DB Provider for SQL Server (0x80040E25)
Row handles must all be released before new ones can be obtained.
/a.asp, line xx


When you use server-side cursors for a recordset (CursorLocation = adUseServer), which is the default cursor, some OLE DB providers only support one pending row update.

SQL Server 6.5 and 7.0 only support adLockBatchOptimistic when the cursor type is keyset-driven or static with server-side cursor. In addition, keyset-driven cursors can only be supported with tables that have unique indexes.


To resolve this issue, use a client-side cursor. To do this, set the value of the recordset's CursorLocation property to adUseClient as follows:
  oRs.CursorLocation = adUseClient


Steps to Reproduce Behavior

  1. Create a new Active Server Pages (ASP) page, and paste the following code:
      Set oConn = Server.CreateObject("ADODB.Connection")
      Set oRS = Server.CreateObject("ADODB.Recordset")
      oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Test.mdb"
      Set oRs.ActiveConnection = oConn
      'oRs.CursorLocation = adUseClient 'To resolve this issue, uncomment this line.
      oRS.CursorType = adOpenKeySet
      oRS.LockType = adLockBatchOptimistic    
      oRS.Open "TestTableName"
      oRS("ID") = "10"
      oRS("Description") = "Testing adLockBatchOptimistic"
      oRS("ID") = "11"
      oRS("Description") = "Testing adLockBatchOptimistic"
      Set oRs = Nothing
      Set oConn = Nothing
  2. Run the ASP page.


Article ID: 261297 - Last Review: February 28, 2014 - Revision: 4.1
  • Microsoft Active Server Pages 4.0
  • Microsoft ActiveX Data Objects 2.0
  • Microsoft ActiveX Data Objects 2.01
  • Microsoft ActiveX Data Objects 2.1
  • Microsoft ActiveX Data Objects 2.1 Service Pack 1
  • Microsoft ActiveX Data Objects 2.1 Service Pack 2
  • Microsoft ActiveX Data Objects 2.5
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
kbnosurvey kbarchive kberrmsg kbprb KB261297

Give Feedback


Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from