Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The documentation clearly states that, if you open a <code>Recordset</code> that has no records:</p> <ul> <li><code>BOF</code> will be true</li> <li><code>EOF</code> will be true</li> <li><code>RecordCount</code> will be <code>0</code></li> </ul> <p>For a non-empty <code>Recordset</code>, neither <code>BOF</code> and <code>EOF</code> are true until you move beyond the first or last record.</p> <p>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?<br> It could be the result of a race condition.</p> <p>Rather than use <code>BOF</code> or <code>EOF</code>, you can test on <code>Recordcount</code>: it's always <code>0</code> if the recordset is empty.<br> If the recordset is not empty, it will usually return <code>1</code> right after the recordset has been open; <code>Recordcount</code> isn't an expensive operation in that case.<br> The only way to really return the <em>actual</em> number of records is to issue a <code>MoveLast</code> before calling <code>Recordcount</code> to force all records to be loaded.</p> <p>Usually, if I need to iterate through a resultset in read-only fashion:</p> <pre><code>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 </code></pre> <p>If I don't need to iterate through records but just test if anything was returned:</p> <pre><code>Set rs = db.OpenRecordSet("...", dbOpenForwardOnly) If Not (rs Is Nothing) Then With rs If .RecordCount &gt; 0 Then ' We have a result ' Else ' Empty resultset ' End If .Close End With Set rs = Nothing End If Set db = Nothing </code></pre> <p>It's pretty defensive and you have to adapt to your circumstances, but it works correctly every time.</p> <p>Regarding your 2nd question, testing (<code>BOF</code> Or <code>EOF</code>) after opening the recordset should be more foolproof than the <code>And</code> version, although I'd use <code>Recordcount</code> myself.</p> <p><strong>Edit following your revised question:</strong></p> <p>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 <code>ORDER BY</code> clause.<br> The problem is that you are expecting the resultset to be in the <code>Begin Order</code> followed by <code>End Order</code> sequence but your SQL Statement doesn't guarantee you that.<br> 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:</p> <ul> <li>It's always going to happen that way</li> <li>That the original data was saved in the expected sequence, resulting in IDs that are in the 'wrong' order.</li> </ul> <p>So, whenever you have expectations about the sequence of the resultset, you must explicitly order it.</p> <p>I would also refactor this bit of code:</p> <pre><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 &lt;&gt; True And rs.eof &lt;&gt; True Then myrec.movelast If rs.fields("type").value = BeginOrder Then OrderOpen = True End If End If </code></pre> <p>Into a separate function similar to:</p> <pre><code>' 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 &amp; "SELECT Type " sql = sql &amp; "FROM Orders " sql = sql &amp; "WHERE ((type = OrderBegin) OR (type = OrderEnd)) " sql = sql &amp; " AND (CustID=" &amp; CustID &amp; ")" sql = sql &amp; "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 &gt; 0 Then result = (rs!type = BeginOrder) End If rs.Close End If Set rs = Nothing Set db = Nothing IsOrderOpen = result End Function </code></pre> <p>This would make the whole thing a bit more robust.</p>
    singulars
    1. This table or related slice is empty.
    1. POWhy extremely occasionally will one of bof/eof be true for a new non-empty recordset
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USRenaud Bompuis
    1. USRenaud Bompuis
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks very much for this
      singulars
      1. PO
      1. USjjb
    2. CORenaud, you probably won't believe me but there is a "order by ID" in the SQL. The reason I saying that is that for me the core issue is whether a non-empty recordset can open with the pointer pointing at either bof or eof and I just want to rule out the option that maybe the records were returned in incorrect order. The problem I have is that if this isn't the case I can't see any answer in my code to why this occurred. I googled this and came across 1 or 2 other old examples where this seems to have happened to others. I also ran a 800,000 recordset simulation and it didn't happen once
      singulars
      1. PO
      1. USjjb
    3. COI was really just hoping to be able to close this bug off with an explanation that it was caused by the expression rs.bof <> true and rs.eof <> true evaluating to false when there were actualy records present. Then I could sleep easier!
      singulars
      1. PO
      1. USjjb
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload