Tutorial :Why extremely occasionally will one of bof/eof be true for a new non-empty recordset



Question:

 set recordsetname = databasename.openrecordset(SQLString)      if recordsetname.bof <> true and recordsetname.eof <> true then      'do something      end if  

2 questions :

  1. the above test can evaluate to false incorrectly but only extremely rarely (I've had one lurking in my code and it failed today, I believe for the first time in 5 years of daily use-that's how I found it). Why very occasionally will one of bof/eof be true for a non-empty recordset. It seems so rare that I wonder why it occurs at all.

  2. Is this a foolproof replacement:

    if recordsetname.bof <> true or recordsetname.eof <> true then  

Edit to add details of code :

Customers have orders, each order begins with a BeginOrder item and end with an EndOrder item and in between are the items in the order.

The SQL is:

' ids are autoincrement long integers '  SQLString = "select * from Orders where type = OrderBegin or type = OrderEnd"               Dim OrderOpen as Boolean  OrderOpen = False    Set rs = db.Openrecordset(SQLString)  If rs.bof <> True And rs.eof <> True Then      myrec.movelast      If rs.fields("type").value = BeginOrder Then           OrderOpen = True      End If  End If    If OrderOpen F False Then      'code here to add new BeginOrder Item to Orders table '  End If    ShowOrderHistory 'displays the customer's Order history '  

In this case which looks this this

BeginOrder  Item a  Item b  ...  Item n  EndOrder    BeginOrder  Item a  Item b  ...  Item n  EndOrder    BeginOrder  Item a  item b  ...  Item m    BeginOrder     <----should not be there as previous order still open  


Solution:1

The documentation clearly states that, if you open a Recordset that has no records:

  • BOF will be true
  • EOF will be true
  • RecordCount will be 0

For a non-empty Recordset, neither BOF and EOF are true until you move beyond the first or last record.

Could it be that, from time to time, someone else could have added/deleted a record to one of the tables in the recordset you're just opening and change the resultset?
It could be the result of a race condition.

Rather than use BOF or EOF, you can test on Recordcount: it's always 0 if the recordset is empty.
If the recordset is not empty, it will usually return 1 right after the recordset has been open; Recordcount isn't an expensive operation in that case.
The only way to really return the actual number of records is to issue a MoveLast before calling Recordcount to force all records to be loaded.

Usually, if I need to iterate through a resultset in read-only fashion:

Dim db as DAO.Database  Dim rs as DAO.RecordSet    Set db = CurrentDB()  Set rs = db.OpenRecordSet("...", dbOpenForwardOnly)  If Not (rs Is Nothing) Then      With rs         Do While Not .EOF              ' Do stuff '              .MoveNext         Loop         .Close      End With      Set rs = Nothing  End If  Set db = Nothing  

If I don't need to iterate through records but just test if anything was returned:

Set rs = db.OpenRecordSet("...", dbOpenForwardOnly)  If Not (rs Is Nothing) Then      With rs         If .RecordCount > 0 Then            ' We have a result '         Else            ' Empty resultset '         End If         .Close      End With      Set rs = Nothing  End If  Set db = Nothing  

It's pretty defensive and you have to adapt to your circumstances, but it works correctly every time.

Regarding your 2nd question, testing (BOF Or EOF) after opening the recordset should be more foolproof than the And version, although I'd use Recordcount myself.

Edit following your revised question:

From the bit of code you added to your question, I see a couple of issues, the main one being that your SQL Statement is missing and ORDER BY clause.
The problem is that you are expecting the resultset to be in the Begin Order followed by End Order sequence but your SQL Statement doesn't guarantee you that.
In most cases, since you're using an autoincrement as ID, the database engine will return the data in that natural order, but there is no guarantee that:

  • It's always going to happen that way
  • That the original data was saved in the expected sequence, resulting in IDs that are in the 'wrong' order.

So, whenever you have expectations about the sequence of the resultset, you must explicitly order it.

I would also refactor this bit of code:

' ids are autoincrement long integers '  SQLString = "select * from Orders where type = OrderBegin or type = OrderEnd"               Dim OrderOpen as Boolean  OrderOpen = False    Set rs = db.Openrecordset(SQLString)  If rs.bof <> True And rs.eof <> True Then     myrec.movelast      If rs.fields("type").value = BeginOrder Then          OrderOpen = True      End If  End If  

Into a separate function similar to:

' Returns true if the given CustID has a Open Order, '  ' false if they are all closed.'  Public Function IsOrderOpen(CustID as Long) As Boolean      Dim result as Boolean      result = False        Dim sql as String      ' Here I assume that the Orders table has a OrderDateTime field that '      ' allows us to sort the order in the proper chronological sequence '      ' To avoid loading the complete recordset, we sort the results in a way '      ' that will return the last used order type as the first record.'      sql = sql & "SELECT Type "       sql = sql & "FROM Orders "      sql = sql & "WHERE ((type = OrderBegin) OR (type = OrderEnd)) "      sql = sql & "      AND (CustID=" & CustID & ")"      sql = sql & "ORDER BY OrderDateTime DESC, Type DESC;"        Dim db as DAO.Database      Dim rs as DAO.Recordset      Set db = CurrentDB()      Set rs = db.Openrecordset(sql, dbOpenForwardOnly)        If Not (rs Is Nothing) Then          If rs.RecordCount > 0 Then              result = (rs!type = BeginOrder)          End If          rs.Close      End If        Set rs = Nothing      Set db = Nothing        IsOrderOpen = result  End Function  

This would make the whole thing a bit more robust.


Solution:2

@Renaud Bompuis's answer is quite good. Let me emphasize the point that the DAO Recordcount is never zero for a non-empty recordset, and that is the only thing I ever test in determining if a recordset has returned records. I use .EOF for looping through the records, but don't start stepping through the records until I've already tested if there are records returned.


Solution:3

I occasionally come across the exact same bug in access (had it today in Access 2007 linked to a sql server back end) where the statement

if rst.bof and rst.eof

evaluates to false despite rst representing an empty recordset. When it happened, VBA started and the debugger in the immediate pane showed that, indeed rst.bof was true and rst.eof was true, so it seems to happen for a millisecond and then is corrected, but after one has tested the logic.


Solution:4

The pattern I have always used is:

Set rs = db.OpenRecordset(...)    Do while Not rs.EOF        ' Rest of your code here.        rs.MoveNext  Loop  

I have never seen this fail (yet!). This is described here: How to: Detect the Limits of a DAO Recordset

As an aside, Allen Browne's VBA Traps: Working with Recordsets might be of interest.


Solution:5

This is DAO, right? I'm more an ADO man myself but IIRC there are circumstances (dynaset?) where you need to navigate EOF in order for the final number of rows to be assessed. Could it be in this state that EOF is true, BOF is false (because it hasn't been navigated yet) but as soon as BOF is navigated it is true (obviously) and EOF remains true. Presumably the initial state when zero rows are expected is supposed to be instantaneous but a once-in-five-years freak timing incident means you captured it in a really early initial state?


Solution:6

Here's a possible solution

It could be that your form or module has gotten corrupted. Export/Import the affected module or form, or try the /decompile option. In my case a query was coming back empty when it shouldn't have, but I think the core problem could be similar.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »