SOLVED with RecordSets, EOF=True,BOF=True,RecordCount=2800

bj6671

New Member
Joined
Jul 25, 2007
Messages
16
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!"
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

bj6671

New Member
Joined
Jul 25, 2007
Messages
16
I've been playing with it more. I tried opening the recordset using a new connection (dim cn as new adodb.connection) rather then using the connection I already had (currentdb.connection).

So far, either the glitch just hasn't glitched, or it may have fixed the problem...

If I do have to post the entire codeblock... I'm going to have to obfuscate a lot of it (just the data working parts and the names). The company I'm working for is kinda picky about stuff and could get mad at me if I post too much, even though I'm writing it right now and asking for help. It's not the FBI or CIA, but they can still be touchy.

I'll find out by tomorrow...
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
Are you perhaps deleting records in the code that is not posted? I could see how a delete may cause that kind of issue... just guessing though

hth,
Giacomo
 

bj6671

New Member
Joined
Jul 25, 2007
Messages
16
Yes, I am, refreshing the table. I think that adding a sepperate connection fixed it. However, just incase someone else runs into this problem, let me post what I can of the code. Some of it is stored in an excel macro, so the code won't look complete. Also, I'm taking out all the comments where I talk to myself (does anyone else do that? talk to themselves in comments in the code to keep track of what is working, what isn't, and what you want to work better?)

Private Sub btnImport_Click()

Dim objExcel As Excel.Application
Dim strSQL As String
Dim rsTB As New ADODB.Recordset
Dim cnTB As New ADODB.Connection

DoCmd.SetWarnings False

DoCmd.RunSQL "DELETE * FROM tbl_Boring;"
'See, there is the delete line giacomo

Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Open CurrentProject.Path & "\Macro.xls"

objExcel.Application.Visible = False

objExcel.Run "ImportSheet"
'This macro downloads a spreadsheet from the web and pushes it into tbl_Boring
'I made sure it closes it's connection before continuing.
'At this point, tbl_Boring has around 3000 records.

objExcel.Workbooks("tbl_Boring.XLS").Close False
objExcel.Workbooks("Macro.XLS").Close False
objExcel.Quit
Set objExcel = Nothing
Form.Requery

strSQL = "INSERT INTO Boring_Comment ( [Order] ) " _
& "SELECT tbl_Boring.ordnum FROM tbl_Boring " _
& "LEFT JOIN Boring_Comment ON tbl_Boring.ordnum = Boring_Comment.ordnum " _
& "WHERE (((tbl_Boring.ordnum) Not In ([Boring_Comment].[ordnum])));"
DoCmd.RunSQL (strSQL)

'The below line is what fixed the problem, opening a new connection.

cnTB.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=Boring_Database.mdb;" _
& "DefaultDir=" & CurrentProject.Path & ";Uid=Admin;Pwd=;"

rsTB.Open "tbl_Boring", cnTB, adOpenKeyset, adLockOptimistic

If rsTB.BOF = True And rsTB.EOF = True Then GoTo Problem_Error

If rsTB.BOF = False Then rsTB.MoveFirst

Do Until rsTB.EOF
If rsTB("FOOD_DETAIL") = "Apples" Then rsTB("SUGAR") = rsTB("SUGAR") + 2
If rsTB("FOOD_DETAIL") = "Oranges" Then rsTB("SUGAR") = rsTB("SUGAR") - 2
If rsTB("EAT") = "A" Then rsTB("SUGAR") = rsTB("SUGAR") / 2
If rsTB("EAT") = "C" Then rsTB("SUGAR") = rsTB("SUGAR") * 1.5
If Mid(rsTB("ZIP"), 3, 1) = "B" Then rsTB("SUGAR") = rsTB("SUGAR") + 1
rsTB("SUGAR") = rsTB("SUGAR") * rsTB("SERVINGS")
rsTB.Update
rsTB.MoveNext
Loop

DoCmd.SetWarnings True
rsTB.Close
Set rsTB = Nothing
cnTB.Close
Set cnTB = Nothing

MsgBox "Import Complete"

Exit Sub

Problem_Error:

DoCmd.SetWarnings True
rsTB.Close
Set rsTB = Nothing
cnTB.Close
Set cnTB = Nothing

MsgBox "Import Failed." & Chr(13) & "You got that error where the recordset looked empty."

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,798
Messages
5,626,941
Members
416,209
Latest member
tan21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top