I'm having trouble working with recordsets.
I've never seen anything like this before, so it may be some sort of corruption or program error (or maybe it's just a feature I missed).
What is happening is I open a recordset from a table, it then is supposed to step through each record and do stuff, until it reaches EOF.
Set rsTB = CurrentDb.OpenRecordset("tbl_Boring", dbOpenDynaset, dbInconsistent, dbPessimistic)
Do Until rsTB.EOF
rsTB.Edit
' Boring stuff that isn't part of the problem.
rsTB.Update
rsTB.MoveNext
Loop
The problem started when I noticed about half the time, the boring stuff wasn't being run.
I put in a break and found that rsTB.EOF was ALREADY equalling True when I opened the recordset.
I tried putting in a rsTB.MoveFirst but only got an "No Current Record" error. I checked, and both rsTB.EOF AND rsTB.BOF are equalling true! Well, I thought usually means the recordset is empty. so I put in a MsgBox rsTB.Recordcount. It came back 2811 records.
It seemsed not to happen very much orriginally, but now it is happening 4 times out of 5.
The only way I've managed to get it to work consistantly (if you want to call it that) is:
Try_Open:
Set rsTB = CurrentDb.OpenRecordset("tbl_Boring")
If rsTB.BOF = True And rsTB.EOF = True Then
rsTB.Close
Set rsTB = Nothing
GoTo Try_Open
End If
I don't understand why this is causing such a problem. Is it a timing issue where the recordset is so big that it takes a while to completely open and be ready for editing? If that was the case, when it errored out and I went to Debug, the hit Play again, it should have gone through. A delay doesn't seem to help.
So why does a fresh recordset show both an EOF and a BOF, but isn't empty and MoveFirst and MoveLast fail? And why does it seem to be getting worse? If it failed all of the time, I would just assume I mistyped something, but it still works occasionally (and used to work all of the time)> And I HAVE tried a compact and repair.
I even tried switching to an ADO recordset, that seems to have lowered the rejection to 25% again.
edit:
More testing, with the ADO recordset, when it fails it says there are 0 records, which means it just didn't open the table at all. EOF=True, BOF=True, RecordCount=0, AbsolutePosition = -1.
Line is:
rsTB.Open "tbl_Boring", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
The table has a bunch of records in it, so I don't know why it is failing, or why it works when it does!
Another edit:
I put a break (the little red dot that pauses the program) before it opened the recordset, it passed everytime, but when I put a msgbox saying "Pause" it still was hit or miss. I don't know if that will help, but like chicken soup "It coidn't hoyt!"
I've never seen anything like this before, so it may be some sort of corruption or program error (or maybe it's just a feature I missed).
What is happening is I open a recordset from a table, it then is supposed to step through each record and do stuff, until it reaches EOF.
Set rsTB = CurrentDb.OpenRecordset("tbl_Boring", dbOpenDynaset, dbInconsistent, dbPessimistic)
Do Until rsTB.EOF
rsTB.Edit
' Boring stuff that isn't part of the problem.
rsTB.Update
rsTB.MoveNext
Loop
The problem started when I noticed about half the time, the boring stuff wasn't being run.
I put in a break and found that rsTB.EOF was ALREADY equalling True when I opened the recordset.
I tried putting in a rsTB.MoveFirst but only got an "No Current Record" error. I checked, and both rsTB.EOF AND rsTB.BOF are equalling true! Well, I thought usually means the recordset is empty. so I put in a MsgBox rsTB.Recordcount. It came back 2811 records.
It seemsed not to happen very much orriginally, but now it is happening 4 times out of 5.
The only way I've managed to get it to work consistantly (if you want to call it that) is:
Try_Open:
Set rsTB = CurrentDb.OpenRecordset("tbl_Boring")
If rsTB.BOF = True And rsTB.EOF = True Then
rsTB.Close
Set rsTB = Nothing
GoTo Try_Open
End If
I don't understand why this is causing such a problem. Is it a timing issue where the recordset is so big that it takes a while to completely open and be ready for editing? If that was the case, when it errored out and I went to Debug, the hit Play again, it should have gone through. A delay doesn't seem to help.
So why does a fresh recordset show both an EOF and a BOF, but isn't empty and MoveFirst and MoveLast fail? And why does it seem to be getting worse? If it failed all of the time, I would just assume I mistyped something, but it still works occasionally (and used to work all of the time)> And I HAVE tried a compact and repair.
I even tried switching to an ADO recordset, that seems to have lowered the rejection to 25% again.
edit:
More testing, with the ADO recordset, when it fails it says there are 0 records, which means it just didn't open the table at all. EOF=True, BOF=True, RecordCount=0, AbsolutePosition = -1.
Line is:
rsTB.Open "tbl_Boring", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
The table has a bunch of records in it, so I don't know why it is failing, or why it works when it does!
Another edit:
I put a break (the little red dot that pauses the program) before it opened the recordset, it passed everytime, but when I put a msgbox saying "Pause" it still was hit or miss. I don't know if that will help, but like chicken soup "It coidn't hoyt!"
