GebhartBob
asked on
ADO Error - "Row handles must be released ..."
Here's a simple little event Sub:
Private Sub txtActNo_LostFocus()
With rstFile
.Index = "ActNo" ' Error after second "Sorry"
.Seek txtActNo, adSeekFirstEQ
If Not .EOF Then
RecordFoundSub
Else
MsgBox "Sorry, no such record. Try again."
txtActNo.SetFocus
End If
End Sub
It works fine if the record is found, or the first two consecutive times a record is not found. But if a record cannot be found three times in a row, the third time the .Index = "ActNo" command is executed, it fails with this error message:
"Row handles must all be released before new ones can be obtained."
It fails every time, in exactly the same way, hard as a rock.
Ideas, anyone?
Private Sub txtActNo_LostFocus()
With rstFile
.Index = "ActNo" ' Error after second "Sorry"
.Seek txtActNo, adSeekFirstEQ
If Not .EOF Then
RecordFoundSub
Else
MsgBox "Sorry, no such record. Try again."
txtActNo.SetFocus
End If
End Sub
It works fine if the record is found, or the first two consecutive times a record is not found. But if a record cannot be found three times in a row, the third time the .Index = "ActNo" command is executed, it fails with this error message:
"Row handles must all be released before new ones can be obtained."
It fails every time, in exactly the same way, hard as a rock.
Ideas, anyone?
> With rstFile
With statement is using "Row handle"? Try it without "With" statement.
With statement is using "Row handle"? Try it without "With" statement.
ASKER
I have to use the .Index = "ActNo" because the user can employ other access methods (Name, SSN, etc.) which set .Index differently for those access methods.
I tried ameba's idea of getting rid of the "With rstFile". No joy.
I found something that works, but it's a gross kludge. On detecting a "Not found", I close the recordset and then re-open it, and the problem goes away. Why that should be necesary is anybody's guess.
Some additional facts: I opened this file with direct access to the underlying table, with these parameters:
CursorLocation = adUseServer
adLockType = adLockOptimistic
options: adCmdTableDirect
The ADO error I'm getting is hex 80040e25, which none of my help files or books ever heard of. I wisj I had a nickel for every such undocumented ADO error code.
The root of my problem is that I haven't the foggiest idea what a "Row handle" is, nor why you have to release them before new ones can be obtained.
Sigh ...
I tried ameba's idea of getting rid of the "With rstFile". No joy.
I found something that works, but it's a gross kludge. On detecting a "Not found", I close the recordset and then re-open it, and the problem goes away. Why that should be necesary is anybody's guess.
Some additional facts: I opened this file with direct access to the underlying table, with these parameters:
CursorLocation = adUseServer
adLockType = adLockOptimistic
options: adCmdTableDirect
The ADO error I'm getting is hex 80040e25, which none of my help files or books ever heard of. I wisj I had a nickel for every such undocumented ADO error code.
The root of my problem is that I haven't the foggiest idea what a "Row handle" is, nor why you have to release them before new ones can be obtained.
Sigh ...
try changing the location of the cursor to UseClient, and set your locktype to adLockPessimistic. Might help....
CJ.
CJ.
It is perfectly valid to change the Index, I was simply questioning the necessity of setting it every time you do a Seek. In other words the LostFocus is probably not the best place for it, but rather wherever the user decides to use a different search, not knowing your interface it is a little difficult to guess.
However, if you feel that the only place is the LostFocus event than try something like:
If .Index <> "ActNo" Then
.Index = "ActNo"
End If
Anthony
However, if you feel that the only place is the LostFocus event than try something like:
If .Index <> "ActNo" Then
.Index = "ActNo"
End If
Anthony
As to the runtime errors they are not undocumented, these are usually composite numbers so you will never find 80040e25 as this is composed of multiple errors. In any case, you should be using the ADO Errors collection and not VB's Error object to get all the errors. Something like:
Dim er As ADODB.Error
On Error GoTo ErrHandler
...
Exit Sub
ErrHandler:
For Each er In cn.Errors
Debug.Print CStr(er.Number) & ": " & er.Description
Next
Dim er As ADODB.Error
On Error GoTo ErrHandler
...
Exit Sub
ErrHandler:
For Each er In cn.Errors
Debug.Print CStr(er.Number) & ": " & er.Description
Next
ASKER
To acperkins---
I'm having trouble implementing your error handling routine, which was news to me.
I get an error #91 on this line:
For Each er In cn.Errors
I set up "cn" via "Dim cn As Connection", but I didn't initialize it, and that's what's causing the trouble. How should I set "cn"?
---Bob Gebhart
I'm having trouble implementing your error handling routine, which was news to me.
I get an error #91 on this line:
For Each er In cn.Errors
I set up "cn" via "Dim cn As Connection", but I didn't initialize it, and that's what's causing the trouble. How should I set "cn"?
---Bob Gebhart
BUG: Error Message "Rows Must Be Released" with SQLOLEDB and ADO Recordset Events
http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;Q257731
http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;Q257731
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ameba---
You are amazing! The bug report article wasn't 100% on point, but it was close enough. My situation was different that the one described in the bug report, but it produced the same symptom.
Their fix of .MoveNext followed by .MovePrevious wouldn't fly in my case, because my .Seek failed to find a record.
The fix was to call .Requery, which effectively closes and then re-opens the file ... that's the kludge I applied. Requery, though, is faster, cleaner, and simpler.
There's Weapon #62,742 in my bag of VB tricks, thanks to you. I deeply appreciate the time you took to enlighten me.
---Bob
You are amazing! The bug report article wasn't 100% on point, but it was close enough. My situation was different that the one described in the bug report, but it produced the same symptom.
Their fix of .MoveNext followed by .MovePrevious wouldn't fly in my case, because my .Seek failed to find a record.
The fix was to call .Requery, which effectively closes and then re-opens the file ... that's the kludge I applied. Requery, though, is faster, cleaner, and simpler.
There's Weapon #62,742 in my bag of VB tricks, thanks to you. I deeply appreciate the time you took to enlighten me.
---Bob
Thanks, Bob :-)
The only reference I could find in MSDN, to this error message was when using adLockBatchOptimistic.
But in any case, if you can post the code where you open the recordset and for the RecordFoundSub it may clarify.
Anthony