title: Why would a query that returns 835 records in Access, return an empty recordset in VBA code?
body: <p>The following code is slightly modified from some boilerplate I use for almost every project I work on (in Access).  All the other versions of this code work perfectly in this db.  The tables are all actually in SQL Server but they are linked in this Access db.</p>
  
  <p>Here's the weird part...When I run the code and get down into the "With rs" and do the ".Open", the next step "Do While Not .EOF" skips to the bottom of the "Do"...I am at .EOF!  But if I get a copy of the ".Source" SQL Statement and paste it into a blank SQL query on the non-code Access main window, I get 835 records from that query.  How could I be getting back an empty recordset when this query should return 835 records?</p>
  
  <p>I've tried changing my .CursorType and .LockType, to no avail.</p>
  
  <p>Anyone have any thoughts about what I could try in troubleshooting this?</p>
  
  <pre><code>Public Sub test()
  Dim db As ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim sFromDate As String
  Dim sToDate As String
  Dim sSubUnit As String
  
  sFromDate = "1/1/2013"
  sToDate = "1/31/2013"
  sSubUnit = "2???"
  
  ' Make a connection to the db
  Set db = CurrentProject.Connection
  
  With rs
      .ActiveConnection = db
      .CursorType = adOpenForwardOnly
      .LockType = adLockReadOnly
  
      .Source = "SELECT SCEVENT.CLIENT_ID, SCATTEND.EMP_ID, SCATTEND.STARTDATE, SCATTEND.SVC_ID " &amp; _
                  "FROM SCATTEND LEFT JOIN SCEVENT ON SCATTEND.SCEVENT_ID = SCEVENT.ID " &amp; _
                  "WHERE (SCATTEND.STARTDATE Between #" &amp; sFromDate &amp; "# And #" &amp; sToDate &amp; "#) " &amp; _
                      "AND (SCATTEND.SUBUNIT_ID Like '" &amp; sSubUnit &amp; "') " &amp; _
                      "AND ((APPTYP_ID IS NULL) OR (APPTYP_ID IN (1,2))) " &amp; _
                  "ORDER BY SCEVENT.CLIENT_ID, SCATTEND.EMP_ID, SCATTEND.STARTDATE, SCATTEND.SVC_ID;"
      .Open
      Do While Not .EOF
          MsgBox "there are records!"
          .MoveNext
      Loop
      .Close
  End With
  
  Set rs = Nothing
  Set db = Nothing
  </code></pre>
  
  <p>End Sub</p>